Split Text and Numbers

Split-Text-and-Numbers

To split text and numbers, use a formula based on the FIND function, the MIN function, and the LEN function with the LEFT or RIGHT function.

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

Formula to split Text and Numbers

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, which is the position of the “1” character in the original string. Locating this position is important when applying string manipulation functions or splitting or extracting strings.

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). If we use the SplitNumbers(string, 0) formula, we’ll get the text part 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 practice file.

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