The gist of this formula is to replace a given delimiter with a large number of spaces using SUBSTITUTE and REPT, then use the MID function to extract text related to the "nth occurrence" and the TRIM function to get...

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

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

The formula shown in this example uses a series of nested SUBSTITUTE functions to strip out parentheses, hyphens, colons, semi-colons, exclamation marks, commas, and periods. The process runs from the inside out, with...

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 TRIM function is fully automatic. It removes removes both leading and trailing spaces from text, and also "normalizes" multiple spaces between words to one space character only. All you need to do is supply a...

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

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

This formula takes advantage of the fact that TRIM will remove any number of leading spaces. We look for line breaks and "flood" the text with spaces where we find one. Then we come back and grab text from the right....

The PROPER function automatically reformats text so that all words are capitalized. At the same time, it lowercases all other text.
If you also need to strip out extra spaces in the names, you can wrap PROPER in the...

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

This formula is an interesting example of a "brute force" approach that takes advantage of the fact that TRIM will remove any number of leading spaces.
Working from the inside out, we use the SUBSTITUTE function to...

The gist: this formula "floods" the space between words in a text string with a large number of spaces, finds and extracts the substring of interest, and uses the TRIM function to clean up the mess.
Working from the...

At the core, this formula replaces all forward slashes (/) with 100 spaces, then extracts 100 characters from the RIGHT and cleans this up with the TRIM function.
The replacement is done with SUBSTITUTE and REPT here...

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