Notice the location of the underscore is different in each row.
This means the formula needs to locate the position of the underscore character first before any text is extracted.
There are two basic approaches to solving this problem.
If you are using Excel 365, the best approach is to use the TEXTBEFORE and TEXTAFTER functions.
Both approaches are explained below.
Choose the best option based on your specific needs.
The result is “Assessment in cell C5 and “January 10” in cell D5.
As the formula is copied down it performs the same operation on all values in column B.
As this formula is copied down, it will return the same results seen in column C above.
As this formula is copied down, it will return the same results seen in column D above.
TEXTAFTER Function
The Excel TEXTAFTER function returns the text that occurs after a given substring or delimiter.
For example, =LEFT(“apple”,3) returns “app”.
For example, =RIGHT(“apple”,3) returns “ple”.
LEN will also count characters in numbers, but number formatting is not included.
When the text is not found, FIND returns a #VALUE error.
Related videos
Excel TEXTSPLIT function
Excel TEXTBEFORE function
Excel TEXTAFTER function
