In this example, the goal is to split a text string at the underscore("_") character with a formula. Notice the location of the underscore is different in each row. This means the formula needs to locate the position of...

Excel doesn't have a dedicated function for counting words in a cell. However, with a little ingenuity, you can create such a formula using the SUBSTITUTE and LEN functions, with help from TRIM, as shown in the example...

At the core, this formula uses the RIGHT function to extract characters starting from the right. The other functions which make up the complex part of this formula just do one thing: they calculate how many characters...

This formula works by using SUBSTITUTE to first remove all of the characters being counted in the source text. Then the length of the text (with the character removed) is subtracted from the length of the original text...

When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the rule is evaluated for each cell in B4:G12...

The key to this formula is the MATCH function, which is set up like this:
MATCH(MAX(LEN(name)),LEN(name),0))
In this snippet, MATCH is set up to perform an exact match by supplying zero for match type. For lookup...

The first expression uses LEFT and UPPER to capitalize the first letter:
=UPPER(LEFT(B5))
No need to enter 1 for num_chars in LEFT, since it will default to 1. The second expression extracts the remaining characters...

Working from the inside out, the LEN function runs on the range B5:B11. Because we give LEN multiple values, it returns multiple results in an array like this:
{127;78;43;112;59;72;154}
This array is evaluated...

At the core, this formula takes a text string with spaces, and "floods" it with additional spaces by replacing each space with a number of spaces using SUBSTITUTE and REPT. The number of spaces used is based on the...

The LEN function is fully automatic. In the example, the formula in the active cell is:
=LEN(B5)
The LEN function simply counts all characters that appear in a cell. All characters are counted, including space...

At the core, this formula extracts characters from the right with the RIGHT function, using FIND and LEN to figure out how many characters to extract. C4 contains the text "achang@maaker.com", so LEN returns 17...

Working from the inside out, the MID function is used to generate an array from text entered in B5 with this snippet:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
explained in detail here. The result is an...

When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. So, in this case the formula =ISBLANK(B4) is evaluated for...

The LEFT function is perfect for extracting characters starting from the left side of a text string. We use LEFT in this formula to extract all characters up to the number of characters we want to trim.
The challenge,...

SUMPRODUCT accepts the range B3:B6 as an array of four cells. For each cell in the array, LEN calculates the length of the text as a number. The result is an array that contains 4 numbers. SUMPRODUCT then sums the...