How to Separate Numbers From Text in Excel (Step-by-Step)

You will not be the first ones to have badly imported or badly managed data. One such example is text and numbers in the same cell. What use are all those rows and columns then? The numbers are most likely details related to the textual element of the text string e.g. quantity, price, size, model number, age. For the sake of organizing, clarity, and quite importantly for sorting of the data, it would be fitting to separate the text and numbers into separate columns. That way you can sort the data easily according to the text or the numbers.

Our tutorial today will give you detailed methods on separating numbers from text using the Text to Columns and Flash Fill features, formulas, and VBA. We have explained how each method works and how you can customize each method in line with your data.

Let's get separating!

Method #1 – Using Text to Columns Feature

Text to Columns is a very helpful tool that can split the text from one cell into multiple cells according to the specifications you provide in the Text to Columns wizard. This is helpful for us as we can use Text to Columns to separate the names and employee codes in our sample data to separate columns. In our sample data, the names and codes are separated by a hyphen as a delimiter so we will specify that the text is separated into text before and after the delimiter. Here are the steps to separate a text string into two columns using the Text to Columns feature:

• Select the cells containing the text and numbers.
• Go to the Data tab and select the Text to Columns button in the Data Tools

• Select the Delimited radio button from the two options and then hit the Next command button.

• Select the Other checkbox and type a hyphen "-" in the provided field. Hit the Next command button again.

• The destination will automatically be the first selected cell. If you want to overwrite the first column, simply click on the Finish button. We want our data to be split into two new columns so we will set the destination as the first cell of the next column:
• Set the destination of the two new columns by typing the cell reference of the first cell of the destination in the Destination
• We want our data to start from the column next to the one selected so the destination for our example will be "$C$3".
• When done, hit the Finish command button.

• Click the OK button on the prompt that appears.

The selected column will be split into text and numbers to the set destination, ignoring the delimiter.

Method #2 – Using Flash Fill Feature

This is probably going to be the quickest, easiest, and most customized method of separating numbers from text. Flash Fill automatically fills in values in a column according to one or two examples manually provided by you. The beauty of this feature is that you don't have to visit any dialog boxes or settings or even remember any formulas.

For the example we have, this feature is especially helpful as we can simply extract the names and codes without the inclusion of any leading or trailing spaces or even any delimiters. Since we are filling out two columns, Flash Fill will have to be applied twice to two different columns. Below are the steps to separate the numbers from the text using Flash Fill:

• In a new column next to the column with the data, type out the first part of the text you want separated and press the Enter key. In our example, we will type the employee name.
• Select the Flash Fill feature from the Fill button in the Home tab's Editing Alternatively, you can use the keyboard shortcut for Flash Fill which is Ctrl + E.

• The first column will flash-fill with just the names:

• Now do the same for the next column and manually fill out the first cell of the new column with the second part of the text. We will fill out the employee code.

• Use the Flash Fill feature from the ribbon menu or by pressing the Ctrl + E
• The numbers will also fill out, thanks to Flash Fill.

Method #3 – Using LEFT, RIGHT & SEARCH Function-based Formulas

Right off the bat, the formulas we are using are tailored to our case example where the text and number are separated by a space, hyphen, and another space. If the text and numbers in your dataset are separated by delimiters that do not have space characters, you will have to tweak the formulas used below (change the "-2" in the first formula to "-1" and eliminate the "-1" from the second formula). Also, our delimiter is a hyphen and we have used hyphens in the formulas. Replace the hyphens in the formula with the delimiter in your data. Alternatively, you can use the Find and Replace feature to change your delimiters.

Another pointer, if the text or numbers in your data are a fixed number of characters, you do not need to use the detailed formulas below.

For a fixed number of characters in the starting text, you can use the LEFT formula with the number of characters e.g.

For a fixed number of characters in the second part of the text, like our case example, you can use the RIGHT function e.g.

But if we change things up a little in our data, with some irregular numbering, the formulas will have to change significantly. We have changed a couple of the numbers in our example so you know how the formulas apply to different number of characters in the numbers too:

Let's move on to the formulas. We are using 2 different formulas for extracting each; the text and the numbers.

The first formula we have for separating the employee names from column B:

We are using this formula to find all the text in cell B3 starting from the left and up to the delimiter "-" minus 2 characters. The SEARCH function locates the position of the delimiter in the text string in B3. The LEFT function returns the text from the start of the text string up, the number of characters to be extracted being defined by the position of the delimiter using the SEARCH function.

If the formula ended here, the result would be "Adrian Adamson -". The "-2" part of the formula comes in handy here two return 2 characters lesser so that the space character and delimiter don't make it to the final outcome of the function. Therefore, we have "Adrian Adamson" as the result.

For the numbers part of the text, we take the following formula:

The SEARCH function has been used exactly the same way as before, to locate the position of the delimiter. The RIGHT function returns the characters from the end of the text string in B3. How many characters the RIGHT function has to return is determined by the LEN and SEARCH functions.

The LEN function returns the total number of characters in the text string i.e. 22 and the SEARCH function locates the position of the delimiter i.e. 16. LEN minus SEARCH becomes 22-16=6. The number of characters returned from B3 would be 6 which would include a leading space. To avoid leading spaces, we have added "-1" to the formula which then would return the last 5 characters "93114" as the result.

Method #4 – Using VBA

VBA (Visual Basic for Applications) is a language programming tool for Office applications. With VBA we can automate our Office applications tasks which are especially helpful for large datasets. To split numbers from text in Excel, we will write a code in our sheet using VBA which will serve as a user-defined function to return the numeric values from the text string. And then we will use the function as an opposite to return all text other than numeric values.

So what does this mean for delimiters? The delimiters will be returned in the oppositely applied function; as part of all the text other than numeric values. While you can use the results including the delimiters and remove them later, for easy application, we will use Find and Replace to get our data ready for VBA and remove the delimiters. Let's quickly go through replacing delimiters:

Select the column with the data and press the Ctrl + H keys. Replace the delimiter (" – " in our case) with a single space character:

Select the Replace All button and this is what our data will look like now:

Now let's see the steps on how VBA works for separating numbers from the text:

• Press the Alt + F11 keys to open the VB If you have the Developer tab enabled, you can open the VB editor from there. Here's the VB editor:

• For launching a Module window, open the Insert menu and select Module.

• Here we have the Module window for entering our code:

• Copy and paste the following code, for separating numbers from a text string, in the editable field in the Module window:

Public Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
Dim xLen As Long
Dim xStr As String
xLen = VBA.Len(pWorkRng.Value)
For i = 1 To xLen
 xStr = VBA.Mid(pWorkRng.Value, i, 1)
 If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
 SplitText = SplitText + xStr
 End If
Next
End Function

The code is written as a user-defined function that needs two arguments to work; the text string containing the numbers and a Boolean value (either TRUE or FALSE).

• Close the VB editor to go back to the worksheet.
• Enter the following formula into a separate column for extracting the text from the values in column B:

With FALSE as part of the function, all the values in the text string other than the numeric values are returned.

• Apply this formula to a separate column for the number values from the text string:

With TRUE in the function, the function returns all the numeric values from the text string.

Time to separate the end from the guide. Those were our best tips on separating numbers from text in Excel. Don't be dubious or fearful about switching things around, what matters is that you attempt at making things work for yourself. Otherwise, there's always Ctrl + Z. We hope you found our explanations insightful and our tips easy to apply to your Excel scenario. And with that, Excel sayonara!