How to Separate Text and Numbers in Excel (6 Suitable Ways) -

Get FREE Advanced Excel Exercises with Solutions!

We often have to separate text and numbers from some data in Excel. Today I will be showing 4 easy ways how you can accomplish that conveniently.

Watch Video – Separate Text and Numbers in Excel

Let us have a look at this data set. We have the Combined Data of some students of a school named Saint Xaviers School.

We have two separate columns. Columns C and D, where we want to extract the Student Names and Student IDs separately. How can you do that? Here are the methods you can follow.

Table of Contents hide

1. Using Text to Column Feature to Separate Text and Numbers in Excel

2. Separating Text and Numbers in Excel with Flash Fill from Excel Toolbar

3. Introducing Flash Fill From Fill Handle to Detach Text and Numbers in Excel

4. Detaching Text and Numbers Inserting Excel MIN and FIND Function

5. Combining RIGHT, LEFT, and SEARCH Functions to Separate Text and Numbers

6. Applying Excel VBA Macro to Separate Text and Numbers

Conclusion

1. Using Text to Column Feature to Separate Text and Numbers in Excel

This is the first method I am going to describe here. I will use the Text to Column feature to separate text and numbers in Excel. Let’s follow the steps of the method.

Steps:

Select the cells in which you want to separate text and numbers. Here I select range B4:B13.

Then go to the Data > Text to Columns menu under the Data Tools group.

After that, you will get a Convert Text to Columns Wizard box. As we want to separate text and numbers by a delimiter (comma), check the Delimited option.
Hence, you can see a preview of your data.
Then click Next.

Proceed to step 2 of Convert Text to Columns Wizard.
If you want to separate texts and numbers by comma(,), choose Comma from the Delimiters option.
And of course, you can choose multiple Delimiters together.
Now you will see a preview of your data being split.
Then click Next.

Go to step 3 of the Convert Text to Column Wizard box.
At the bottom of the box, see your data is split into the required number of columns. In this case, in two columns.
Select each column and then in the Column data format option, select the format in which you want to have that column.
In this case, I want both columns to be in General format.
Then in the Destination box, write the Absolute Cell Reference of the leftmost cell of the range where you want your data to be split.
Or click on the small box on its right and manually select the leftmost cell of the Destination range.
Here I selected cell $C$4.
Then click on Finish.

You will find the Student Names and Student IDs split beautifully into two columns like this.

2. Separating Text and Numbers in Excel with Flash Fill from Excel Toolbar

Here, assume in the data set, you do not have the commas (,) in the Combined Data column. Like this. follow the method step by step mentioned below.

Now the above-mentioned process will not work. So how can you separate the texts and numbers this time? Do not worry. The best way to separate now is to use Flash Fill. You can use it in two ways.

Steps:

First, separate the first data manually. Here I put Steve Morris in cell C4 and 101 in cell D4.

Then select the rest of the cells in the first column. I select all cells of range C5:C13.

Go to Data > Flash Fill under the Data Tools section.
Click on Flash Fill.

You will find texts from all cells of column B beautifully arranged in column C.

Do it for the rest of the columns. I do it again for column D.

Read More: How to Split Data in Excel

3. Introducing Flash Fill From Fill Handle to Detach Text and Numbers in Excel

Here, I will use Flash Fill from Fill Handle to separate text and numbers in Excel. Follow the steps given below.

Steps:

Separate the first cell manually. I put Steve Morris in cell C4 and 101 in cell D4.
Drag the Fill Handle of the first column through the rest of the cells. You will get a small icon called Auto Fill Options in the bottom right corner after dragging.

Click the drop-down menu associated with it. You will get four options. Click on Flash Fill.

You will find texts from all cells of column B beautifully arranged in column C.

Then do it for the rest of the cells. I do it again for column D.

Note: Fill Flash is available from Excel 2013. The users of previous versions will not get it.

4. Detaching Text and Numbers Inserting Excel MIN and FIND Function

It will be useful when we have the combined data not separated by commas (,). Like the one, we used in section 2. The MIN function takes an array of numbers as input. Then, returns the minimum number as output. Moreover, the FIND function takes three inputs, find_text (can be a single value or an array), within_text, start_num. Then, returns the position where the find_text input matches inside the within_text text. After that, start searching from the start_num position.

Steps:

First, write down the following formula in cell E5.

=MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789"))

Here,

The FIND function takes input {0,1,2,3,4,5,6,7,8,9}.
This finds the value in cell B5 with the number starting with 0123456789.
The MIN function returns the minimum value from the result of FIND function.

Hence, in the text Steve Morris101, numbers start from the 13th position.
Here, the formula for separating the Name will be:

=LEFT(B4,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B4&"0123456789"))-1)

Here,
The LEFT function takes the arguments from cell B5, and the MIN, and FIND functions return the left value of B5.

Moreover, it separates the name Steve Morris successfully.

Then, drag the Fill Handle to separate names for the rest of the cells.

And for the Student IDs, the formula will be:

=RIGHT(B4,LEN(B4)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},B4&"0123456789"))+1)

Here,
The RIGHT function takes the arguments from cell B5, and the MIN, and FIND functions return the rightmost value of B5.

At last, enter this formula in the first cell and then drag the Fill Handle. You will get the Student IDs separated as well.

Note: If you have numbers first and text after that in the combined data, like 101Steve Morris, then the formula for the numbers will be exchanged with the formula for the text. And you have to use the MAX function in lieu of the MIN function.

Read More: How to Split Cells in Excel

5. Combining RIGHT, LEFT, and SEARCH Functions to Separate Text and Numbers

If the combined data is separated using commas (,) as the data set we used in section 1, then you can use this method. Here, the RIGHT function takes two arguments one text and one position number. Then, the LEFT function takes two arguments: text, and position number. Returns the leftmost part of the text up to the given position number. The SEARCH function takes three inputs, find_text (can be a single value or an array), within_text, and start_num. Then, returns the position where the find_text input matches inside the within_text text. Moreover, starts searching from the start_num position.

Steps:

Now the formula for separating the names will be:

=LEFT(B5,SEARCH(",", B5)-1)

Here,

The SEARCH function searches the comma(,) in cell B5 and returns the position number.
The LEFT function returns the number of characters from the left side based on the value of the SEARCH function.

After that, you will get the separated first student name.

Now, Fill Handle the formula down.

The formula for separating the IDs will be:

=RIGHT(B5,LEN(B5)-SEARCH(",",B5))

Here,

The SEARCH function searches the comma(,) in cell B5 and returns the position number.
Then, subtract this value from the return of the LEN function.
The RIGHT function returns characters based on the subtraction result.

You will find the first result.

Now, Fill Handle the formula down.

Note: If you have numbers first, and then text, like 101Steve Morris, then you have to exchange the formulas.

Read More: How to Split Data with Formula in Excel

6. Applying Excel VBA Macro to Separate Text and Numbers

Now we will separate texts and numbers from a cell using a Macro (VBA Code). Follow the consecutive steps to get the desired result.

Steps:

First of all, press Alt + F11. You will have the VBA window opened.
Then go to the Insert option in the VBA toolbar. Click it.
You will get a few options. Choose Module.

You will get a new Module window.

Now write the following code here.

Public Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
'Updateby Extendoffice
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

We took help from Extendoffice to create and develop the code.

This code creates a new function called SplitText(), which takes two arguments, a combined data and a Boolean value (TRUE or FALSE)
Then, save it as an Excel Macro Enabled Worksheet type.
However, come back to your worksheet. In the Student Name column, insert the formula.

=SplitText(B4,FALSE)

Then, you will get the names separated.

In the Student ID column, insert the formula.

=SplitText(B4,TRUE)

After that, you will get the Student IDs separated.

Student IDs are separated now.

Special Note: There is a specialty of this Macros function which makes it more useful than others. It can separate numbers and text from data where everything is mixed randomly. For example, it can separate 101 and Steve Morris from Steve10 M1orris, which the other methods can not do.

Read More: Split Names in Excel

Download Practice Workbook

Separate Text and Numbers.xlsm

Conclusion

Using these methods, you can separate numbers and text from any cell in Excel. Do you know any other method? Let us know in the comment section.

How to Separate Sheets in Excel

3 Comments

Vincent Sam Nov 21, 2022 at 3:34 PM

Really Usefull, Thank you
3addoula ma5assak Mar 4, 2023 at 1:17 AM

hi, is there any method to separate a mix of numbers and letters automatically? like in chemical equations Cr11C8H18: Cr=11, C=8 and H=18
- Reply
  Bhubon Costa Mar 12, 2023 at 11:24 AM
  Greetings 3ADDOULA MA5ASSAK, thank you for your question. I hope the following codes will solve your issue.
```
 Sub Chemical_Formula()
    Dim inputString As String
    Dim i As Integer
    Dim Char As String
    Dim Text As String
    Dim Number As String
    Dim result As String
    
    inputString = InputBox("Enter the chemical formula:")
    result = ""
    Text = ""
    Number = ""
    
    For i = 1 To Len(inputString)
        Char = Mid(inputString, i, 1)
        If IsNumeric(Char) Then
            Number = Number & Char
        Else
            If Number <> "" Then
                result = result & Text & "=" & Number & ","
                Number = ""
            End If
            If Char Like "[A-Za-z]" Then
                Text = Char
            End If
        End If
    Next i

    If Number <> "" Then
        result = result & Text & "=" & Number
    End If

    MsgBox result
End Sub
 
```
  However, if this doesn’t solve your problem, you can mail us your Excel file with detailed instructions to: [email protected], and we’ll try to solve it as soon as possible.

How to Separate Text and Numbers in Excel (6 Suitable Ways)

1. Using Text to Column Feature to Separate Text and Numbers in Excel

2. Separating Text and Numbers in Excel with Flash Fill from Excel Toolbar

3. Introducing Flash Fill From Fill Handle to Detach Text and Numbers in Excel

4. Detaching Text and Numbers Inserting Excel MIN and FIND Function

5. Combining RIGHT, LEFT, and SEARCH Functions to Separate Text and Numbers

6. Applying Excel VBA Macro to Separate Text and Numbers

Conclusion

Related Articles

Leave a reply Cancel reply