This formula uses the MATCH function in approximate match mode to locate the last text value in a range. Approximate match enabled by setting by the 3rd argument in MATCH to 1, or omitting this argument, which defaults...
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...
This formula concatenates the original value in column B to a string of asterisks (*) assembled with the REPT function so that the final result is always 12 characters:
REPT("*",12-LEN(B5))
Inside the REPT...
This formula uses boolean logic to output a conditional message. If the value in column C is less than 100, the formula returns "low". If not, the formula returns an empty string ("").
Boolean logic is a technique of...
The REPT function simply repeats values. For example, this formula outputs 10 asterisks:
=REPT("*",10) // outputs **********
You can use REPT to repeat any character(s) you like. In this example, we use...
The TEXT function can apply number formats of any kind to numbers. It is most often used when you want to maintain number formatting for a number when concatenating that number with other text.
In this case, the TEXT...
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....
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 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...
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...
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 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...