Split text string at specific character
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.
To extract the text on the left side of the underscore, the formula in C5 is:
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.
To extract text on the right side of the underscore, we use the RIGHT function:
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:
=RIGHT(B5,10) // returns "January 10"
The final result in D5 is the string "January 10".
As the formulas above are copied down, they extract text from the left and right side of the underscore in each row, creating the results seen in columns C and D.