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:
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:
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 entire string.
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:
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:
- string: the cell reference
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.
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.
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: