Advanced Excel Interview Questions Set 7

Intermediate Level

Difference between TEXTJOIN and CONCATENATE Function along with text to column in Excel.

TEXTJOIN and CONCAT are both used to combine text from multiple cells into one. However, they differ in functionality:

- TEXTJOIN allows you to specify a delimiter (such as a comma, space, or other characters) and ignore empty cells while concatenating text.

- CONCAT is a simplified version and doesn't provide the option to use delimiters or ignore empty cells.

Example:

TEXTJOIN Example:

=TEXTJOIN(", ", TRUE, A1:A4) – (second argument is whether to ignore empty cell or not)

- This will concatenate the values in cells A1, A2, A3, and A4, separated by commas and will ignore any empty cells.

If A1 = "John", A2 = "", A3 = "Doe", A4 = "Smith", the result would be:

John, Doe, Smith

CONCAT Example:

=CONCAT(A1:A4)

- This will concatenate all the values in A1:A4 without a delimiter, and it won't ignore empty cells.

If A1 = "John", A2 = "", A3 = "Doe", A4 = "Smith", the result would be:

JohnDoeSmith

Using FIND and SEARCH Functions to Extract Specific Parts of a Text String

Both FIND and SEARCH are used to locate the position of a substring within a text string, but with slight differences:

- FIND is case-sensitive.

- SEARCH is not case-sensitive.

Example:

Assume you have the string in cell A1: "ExcelFunctions2024"

FIND Example:

=FIND("F", A1)

- This will return 6, the position of the uppercase "F".

SEARCH Example:

=SEARCH("f", A1)

- This will return 6, even though the substring in A1 is uppercase "F", since SEARCH is case insensitive.

Extracting a specific part:

You can use these functions with MID to extract a specific part of a string.

=MID(A1, FIND("F", A1), 9)

- This will extract "Functions" from the text in A1

Applying Conditional Formatting to Highlight Cells That Meet Complex Criteria (e.g., Above the 90th Percentile)

To highlight cells above the 90th percentile, follow these steps:

1. First we have to calculated the percentile let say 90th percentile using PERCENTILE()

2. Select the range of data.

3. Go to the Home tab → Conditional Formatting → Top/Bottom Rule.

4. Choose "Above Values” and select the specific value which we have computed.

5. Click Format to set the desired formatting (e.g., bold, red fill).

This formula compares each value in the range A1:A100 to the 90th percentile, and those above it will be formatted.