Split Text and Numbers

Learn how to split text and numbers! Use SplitNumbers function with different parameters depending on whether you want to extract the text or the number.

Generic formula to split Text and Numbers

To separate text and numbers, you can use a formula based on the FIND function, the MIN function, and the LEN function with the LEFT or RIGHT function, depending on whether you want to extract the text or the number. In the example shown, the formula in D4 is:

=MIN(FIND({0,1,2,3,4,5,6,7,8,9},cell_reference&"0123456789"))
=MIN(FIND({0,1,2,3,4,5,6,7,8,9},C4&"0123456789"))
Generic formula to split Text and Numbers

The formula returns 7. This is the position of the number 1 in the string “orange77“.

If you want to apply string manipulation functions, split or extract strings, characters the most important thing to locate the position. We find a virtual delimiter to split text and numbers.

When it’s done, we’ll go to the next step. In the example, we have a string that contains numbers and text. The numeric part of the string appears after the text.

We want to split the text and numbers into two different cells.

Because we have the starting position of the number, just extract the text part using this simple formula:

=LEFT(cell,position_of_the_num_part - 1)

In the example:

=LEFT(C4,7)
extract the text part using this simple formula

Now we have only one step left, split the numbers from the cell.

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

In the example:

=RIGHT(C4,(LEN(C4)-LEN(D4)))
split the numbers from the cell

The result is 77, so we have successfully split the text and numbers from the full string.

Explanation of formula

As we mentioned above, use the FIND function to get the starting position of the number part.

Create an array which contains numbers, like this:

 {0,1,2,3,4,5,6,7,8,9}

This array is equivalent to a “dictionary”, but it does not contain words but numbers. Because the array contains ‘N’ numbers, we’ll get the result in the same format.

In the example, the original text will be converted to:

=7

Quick solution: The SplitNumbers function

Okay, to be fully effective, we’ll show you something special. The UDF-based solution should cut down the time spent in the usual calculations.

Split out text and numbers using UDF

As first let us see the simple code:

Public Function SplitNumbers(ByVal x As String, NumOrText As Boolean) As Variant
Dim y As String, z As String, n As Long
    For n = 1 To Len(x)
        y = Mid(x, n, 1)
        If NumOrText = False Then
            If y Like "[A-Za-z ]" Then z = z & y 'False keeps Letters and spaces only
        Else
            If y Like "[0-9. ]" Then z = z & y   'True keeps Numbers and decimal points
        End If
    Next n
SplitNumbers = Trim(z)
End Function

The syntax of SplitNumbers function:

SplitNumbers(string, num_or_text)

  • string: the cell reference
  • num_or_text

The num_or_text is a Boolean type variable, so it have two states. If we are using the SplitNumbers(string,0) formula with the “0” parameter, we’ll get the text part a result.

Split out text and numbers using UDF

Otherwise, the number part will be remain. Such a logical approach! How to implement the formula into your own workbook?

Press Alt+F11. The Excel VBA editor will appear. Right-click on “ThisWorkbook”. Finally, click “Insert”, click “Module” and paste the code.

Save the workbook as .xlsm format. For more advanced functions try our free excel add-ins!

Related Formulas and resources