Split Text And Numbers

Explanation

Overview

The formula looks complex, but the mechanics are in fact quite simple.

As with most formulas that split or extract text, the key is to locate the position of the thing you are looking for. Once you have the position, you can use other functions to extract what you need.

In this case, we are assuming that numbers and text are combined, and that the number appears after the text. From the original text, which appears in one cell, you want to split the text and numbers into separate cells, like this:

As stated above, the key in this case is to locate the starting position of the number, which you can do with a formula like this:

=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))

Once you have the position, to extract just the text, use:

=LEFT(A1,position-1)

And, to extract just the number, use:

=RIGHT(A1,LEN(A1)-position+1)

In the first formula above, we are using the FIND function to locate the starting position of the number. For the find_text, we are using the array constant {0,1,2,3,4,5,6,7,8,9}, this causes the FIND function to perform a separate search for each value in the array constant. Since the array constant contains 10 numbers, the result will be an array with 10 values. For example, if original text is “apples30” the resulting array will be:

{8,10,11,7,13,14,15,16,17,18}

Each number in this array represents the position of an item in the array constant inside the original text.

Next the MIN function returns the smallest value in the list, which corresponds to the position in of the first number that appears in the original text. In essence, the FIND function gets all number positions, and MIN gives us the first number position: notice that 7 is the smallest value in the array, which corresponds to the position of the number 3 in original text.

You might be wondering about the odd construction for within_text in the find function:

B5&"0123456789"

This part of the formula concatenates every possible number 0-9 with the original text in B5. Unfortunately, FIND doesn’t return zero when a value isn’t found, so this is just a clever way to avoid errors that could occur when a number isn’t found.

In this example, since we are assuming that the number will always appear second in the original text, it works well because MIN forces only the smallest, or first occurrence, of a number to be returned. As long as a number does appear in the original text, that position will be returned.

If original text doesn’t contain any numbers, a “bogus” position equal to the length of the original text + 1 will be returned. With this bogus position, the LEFT formula above will still return the text and RIGHT formula will return an empty string ("").

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 more complicated formula that combines the LEFT, RIGHT, LEN, and FIND functions. Both approaches are explained below.

Note: in the worksheet shown, we split text at the underscore character ("_"), but the solutions below can be adapted to split text at any character.

How to split text in Excel 365

The current version of Excel 365 contains three new functions that make this problem quite simple:

• TEXTSPLIT function - split text at all given delimiters
• TEXTBEFORE function - get the text before a specific delimiter
• TEXTAFTER function - get the text after a specific delimiter

Each option is described below. Choose the best option based on your specific needs.

TEXTSPLIT function

The TEXTSPLIT function splits text at a given delimiter and returns the split text in an array that spills onto the worksheet into multiple cells. In the worksheet shown, the formula used to split text in cell C5 is:

=TEXTSPLIT(B5,"_")

The text comes from cell B5 and the delimiter is provided as the underscore character wrapped in double quotes ("_"). The result is an array with two values like this:

{"Assessment","January 10"}

This array lands in cell C5 and spills into the range C5:D5. 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.

With TEXTBEFORE and TEXTAFTER

Another way to solve this problem is to use the TEXTBEFORE and TEXTAFTER functions separately. You can extract text on the left side of the underscore with the TEXTBEFORE function in cell C5 like this:

=TEXTBEFORE(B5,"_") // left side

As the formula is copied down, it returns the text before the underscore for each text string in B5:B16.

To extract text on the right side of the underscore, use the TEXTAFTER function in cell D5 like this:

=TEXTAFTER(B5,"_") // right side

As the formula is copied down, it returns the text after the underscore for each text string in B5:B16.

How to split text in an older version of Excel

Older versions of Excel do not offer the TEXTSPLIT, TEXTBEFORE, or TEXTAFTER functions. However, you can still split text at a specific character with a more complex formula that combines 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), which is calculated with 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.