If you need to get the nth word in a text string (i.e. a sentence, phrase, or paragraph) you can so with a clever (and intimidating) formula that combines 5 Excel functions: TRIM, MID, SUBSTITUTE, REPT, and LEN.
How...
To get the last relative position (i.e. last row, last column) for text data (with or without empty cells), you can use the MATCH function.
In the example shown, the formula in E5 is:
=MATCH(REPT("z",255),...
To get the last word from a text string, you can use a formula based on the TRIM, SUBSTITUTE, RIGHT, and REPT functions.
In the example shown, the formula in C5 is:
=TRIM(RIGHT(SUBSTITUTE(B5,CHAR(10),REPT("...
To pad text to an equal length using another character, you can use a formula based on the REPT and LEN functions. In the example shown, a formula is used to append a variable number of asterisks (*) to values in column...
To pad a number with zeros, you can use a simple formula based on the TEXT function. To pad with a variable number of zeros, you can add the REPT function.
In the example show, the formula in D6 is:
=TEXT(B6,"...
To split text at an arbitrary delimiter (comma, space, pipe, etc.) you can use a formula based on the TRIM, MID, SUBSTITUTE, REPT, and LEN functions. In the example shown, the formula in C5 is:
=TRIM(MID(SUBSTITUTE($...
To extract the page, or the part of a path after the last forward slash (/), you can use a formula based on several Excel functions: TRIM, RIGHT, SUBSTITUTE, and REPT. In the example shown, the formula in C5 is:
=TRIM...
To get the last word from a text string, you can use a formula based on the TRIM, SUBSTITUTE, RIGHT, and REPT functions. In the example shown, the formula in C6 is:
=TRIM(RIGHT(SUBSTITUTE(B6," ",REPT(...
To extract a word that contains specific text,you can use a formula based on several functions, including TRIM, LEFT, SUBSTITUTE, MID, MAX, and REPT. You can use this formula to extract things like email addresses, or...
To create a simple in-cell histogram, you can use a formula based on the REPT function. This can be handy when you have straightforward data, and want to avoid the complexity of a separate chart.
=REPT(CHAR(110),C11/...
To extract lines from a multi-line cell, you can use a clever (and intimidating) formula that combines 5 Excel functions: TRIM, MID, SUBSTITUTE, REPT, and LEN.
In the example shown, the formula in D5 is:
=TRIM(MID(...
To extract words that begin with a specific character, you can use a formula based on six functions: TRIM, LEFT, SUBSTITUTE, MID, LEN, and REPT. This approach is useful if you need to extract things like a Twitter user...
To display a conditional message, without the IF function, you can use boolean logic and the REPT function. In the example shown, the formula in D5 (copied down) is:
=REPT("low",C5<100)
If the value in...