In the generic version of the formula, rng represents the range to check, and txt is the word or substring to count.
In the example shown, B5:B8 is the range to check, and C2 contains the text (word or substring) to...

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...

This formula uses the MID function to remove the last word from a text string. The main challenge is to figure out where the last word begins.
The formula is a bit convoluted, but the steps are simple. We first count...

Starting from the inside out, the MID function is used to extract all text after "@":
MID(B5,FIND("@",B5),LEN(B5))
The FIND function provides the starting point, and for total characters to extract, we...

At a high level, this formula uses RIGHT to extract characters from the right side of the name. To figure out the number of characters that need to be extracted to get the first name, the formula uses the FIND function...

The LAMBDA function can be used to create reusable, custom functions in Excel without VBA or macros. The first step in creating a LAMBDA function is to verify the formula logic needed in a standard Excel formula. In...

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...

At the core, this formula builds a level 1 and level 2 number and concatenates the two numbers together with a period (".") as a separator. The result is a value like "1.1". The "level 1" number is generated with...

At the core, this formula uses the LEFT function to return text starting from the left. To work out how many characters should be returned, the formula uses this expression:
LEN(B5)-(RIGHT(B5)="/")
Here,...

At the core, this formula looks for a line delimiter ("delim") and replaces it with a large number of spaces using the SUBSTITUTE and REPT functions.
Note: In older versions of Excel on a Mac, use CHAR(13) instead of...

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...

Assuming you have a full name in column B, a first name in column C, and a last name in column D, you can use a formula that looks like this:
=TRIM(MID(B5,LEN(C5)+1,LEN(B5)-LEN(C5&D5)))
At the core, the MID...

The core of this formula is the MATCH function, which locates the position of the longest string using supplied criteria:
MATCH(MAX(LEN(names)*(class=F5)),LEN(names)*(class=F5),0)
Note MATCH is set up to perform an...

For each cell in the range, SUBSTITUTE removes all spaces from the text, then LEN calculates the length of the text without spaces. This number is then subtracted from the length of the text with spaces, and the number...