Exceljet

Quick, clean, and to the point

Split text string at specific character

Excel formula: Split text string at specific character
Generic formula 
=LEFT(text,FIND(character,text)-1)
Summary 

To split a text string at a specific character with a formula, you can use a combination of the LEFT, RIGHT, LEN, and FIND functions. In the example shown, the formula in C5 is:

=LEFT(B5,FIND("_",B5)-1)

And the formula in D5 is:

=RIGHT(B5,LEN(B5)-FIND("_",B5))
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.  

Left side

To extract the text on the left side of the underscore, the formula in C5 is:

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.

Right side

To extract text on the right side of the underscore, we use the RIGHT function:

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 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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.