And the formula in D5 is: As these formulas are copied down, they return the results seen in columns C and D. Note: If you are using an older version of Excel that does not offer the TEXTBEFORE and TEXTAFTER functions, the solution is a bit more complicated, and the standard approach is to use formulas that combine the LEFT, RIGHT, LEN, and FIND functions. See below for details.
TEXTBEFORE and TEXTAFTER
In the current version of Excel, the problem is quite simple. You can extract text on the left side of the delimiter with the TEXTBEFORE function and text on the right side with the TEXTAFTER function. This is the approach shown in the worksheet above, where the formula in C5 is: And the formula in D5 is: As these formulas are copied down, they return the results seen in columns C and D. Done!
Legacy Excel formulas
If you are using an older version of Excel that does not offer the TEXTBEFORE and TEXTAFTER functions, the solution is a bit more complicated, and the standard approach is to use formulas that combine the LEFT, RIGHT, LEN, and FIND functions:
Left side
To extract the text on the left side of the underscore, you can use a formula like this in cell C5: Working from the inside out, this formula uses the FIND function to locate the underscore character ("_") in the text, then subtracts 1 to move back one character: FIND returns 11, so the result after subtracting 1 is 10. This result is fed into the LEFT function as the num_chars argument, the number of characters to extract from B5, starting from the first character on the left: The LEFT function returns the string “Assessment” as the final result. As this formula is copied down, it will return the same results seen in column C above.
Right side
To extract text on the right side of the underscore, you can use a formula like this in cell D5: As above, this formula also uses the FIND function to locate the underscore ("_") at position 11. However, in this case, we want to extract text from the right side of the string, so we need to calculate the number of characters to extract from the right. This is done by subtracting the result from FIND (11) from the total length of the text in B5 (21), calculated the LEN function: The result is 10, which is returned to the RIGHT function as num_chars, the number of characters to extract from the right: The final result in D5 is the string “January 10”. As this formula is copied down, it will return the same results seen in column D above.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
