Explanation
In this example, the goal is to split a text string at the underscore("_") character with a formula. 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. If you are using an older version of Excel without these functions, you can use a formula that combines the LEFT, RIGHT, LEN, and FIND functions. Both approaches are explained below.
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:
=TEXTBEFORE(B5,"_") // left side
And the formula in D5 is:
=TEXTAFTER(B5,"_") // right side
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:
LEFT(B5,FIND("_",B5)-1) // left
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("_",B5)-1 // returns 10
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:
=LEFT(B5,10) // returns "Assessment"
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:
RIGHT(B5,LEN(B5)-FIND("_",B5)) // right
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:
=LEN(B5)-FIND("_",B5)
=21-11
=10
The result is 10, which is returned to the RIGHT function as num_chars, the number of characters to extract from the right:
=RIGHT(B5,10) // returns "January 10"
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.
