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:
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:
Now we have only one step left, split the numbers from the cell.
In the example:
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:
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:
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:
- string: the cell reference
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.
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!