Split Text and Numbers

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

Today’s tutorial is a part of our definitive guide on Excel Formulas.

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 D3 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 “1” character in the original string. If you want to apply string manipulation functions, split or extract strings, the most important thing is to locate the position.

First, we will find a virtual delimiter to split text and numbers. In the example, the string contains numbers and text. However, the numeric part of the string appears after the text, and we want to split the text and numbers into two different cells.

Because we have the starting position of the number, 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 of the string

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

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

In the example:

=RIGHT(B3,(LEN(B3)-LEN(F3)))
extract the numeric part of the string

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

Explanation

As we mentioned above, use the FIND function to get the starting position of the number part. Create an array that 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 includes N numbers, we’ll get the result in the same format.

In the example, the formula will convert the original text to 7.

Apply the SplitNumbers function to split text and numbers

Okay, to be fully effective, we’ll show you something special. First, the UDF-based solution should reduce the time spent on the usual calculations. Our DataFX add-in supports various functions that expand Excel’s built-in function library.

SplitNumbers function syntax:

SplitNumbers(string, num_or_text)
  • string: the cell reference
  • num_or_text

The num_or_text is a Boolean-type variable with two states (0 or 1). We’ll get the text part if we use the SplitNumbers(string, 0) formula with the “0” argument.

how to use the SplitNumbers function in Excel to split the text and numeric parts of a string

Otherwise, the number part will remain. Such a logical approach!

How to implement the formula into your Workbook?

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

Download the Workbook that contains the function.

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

Related Formulas and Resources:

Istvan Vozar

Istvan is the co-founder of Visual Analytics. He helps people reach the top in Excel.