How to Separate Text and Numbers in Excel (4 Easy Ways)

Names separated in Excel

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.


Download Practice Workbook


How to 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.

A Data set in Excel

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.


1.  Using Text to Column Menu from Excel Toolbar

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

A Column Selected in Excel

Then go to the Data>Text to Columns menu in Excel Toolbar under the Data Tools section.

Text to Columns Option in Excel

Click it. You will get a Convert Text to Column Wizard Box. As we want to separate text and numbers by a Delimiter(comma), check the Delimited option.

You can see a preview of your data.

Then click Next.

Convert Text to Column Wizard Box in Excel.

Then you will go to step 2 of Convert Text to Column Wizard.

As you want to separate texts and numbers by comma(,), choose comma(,) from the Delimiters option. You can choose anything more if you need.

And of course, you can choose multiple Delimiters together.

Now you will see a preview of your data being split.

Then click Next.

Convert Text to Column Wizard box in Excel

Now you will be redirected to step 3 of Convert Text to Column Wizard Box. This is the last step.

At the bottom of the box, you see your data 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.

And 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 in its right and manually select the leftmost cell of the Destination range.

Here I select it to be $C$4.

Then click Finish.

Convert Text to Column Wizard box

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

Number and Text separated in Excel


2. Using Flash Fill

Now assume in the data set, you do not have the commas(,) in the Combined Data. Like this.

A new data set in Excel

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.


Way 1: From Excel Toolbar

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

First Data Inserted Manually in Excel

Then select the rest of the cells in the first column. I select all cells from cell C4 to C13.

Cells selected in Excel

Then go to Data>Flash Fill in the Excel Toolbar under the Data Tools section.

Flash Fill Option in Excel

Click on Flash Fill. You will find texts from all cells of column B beautifully arranged in column C.

Click on Flash Fill

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

Numbers separated in Excel


Way 2: From Fill Handle

You can use the Fill Handle of Excel to use the Flash Fill.

Again separate the first cell manually. I put Steve Morris in cell C4 and 101 in cell D4.

Then 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.

Auto Fill Options in Excel

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

Auto Fill Options drop down menus in Excel

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

Texts separated in Excel

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

Numbers separated in Excel

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


3. Using Excel Functions


Method 1: Using a Combination of RIGHT(), LEFT(), MIN() and FIND() functions

It will be useful when we have the combined data not separated by commas(,). Like the one, we used in section 2.

RIGHT() Function

Syntax

=RIGHT(text,[num_char])
  • It takes two arguments, one text, and one position number
  • Returns the rightmost part of the text starting from the given position number from the end.

For example,

RIGHT("Anahita",4) = hita

If you want to know more about RIGHT() Function, visit this link.

LEFT() Function

Syntax

=LEFT(text,[num_char])

It takes two arguments, one text, and one position number.

  • Returns the leftmost part of the text up to the given position number.

For example,

LEFT("Anahita",4) = Ana

If you want to know more about LEFT() Function, visit this link.

MIN() Function

Syntax

=MIN(Number_1,Number_2,...)
  • It takes an array of numbers as input.
  • Returns the minimum number as output.

For example,

MIN(2,5,6,3,7,1)=1

If you want to know more about MIN() function, visit this link.

FIND() Function

Syntax

=FIND(find_text, within_text, [start_num])
  • Takes three inputs, find_text (can be a single value or an array),within_text, start_num.
  • Returns the position where the find_text input matches inside the within_text text.
  • Starts searching from the start_num position.
  • Case sensitive.

For example,

FIND("a","anahita",2) = 3

Now we go back to our task. First, we have to know from which position the numbers start in Combined Data.

The formula for this will be

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

If you want to know more about the FIND() function, visit this link.

A formula in Excel

You see, in the name Steve Morris101, numbers start from 13th position.

So 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)

A complex formula in Excel

See, it separates the name Steve Morris successfully.

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

Dragging Fill Handle in Excel

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)

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

Enter Excel's function formula

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 MAX() in lieu of the MIN() function.


Method 2: Using a Combination of RIGHT(), LEFT() and SEARCH() Function

If the combined data is separated using commas(,), as the data set we used in section 1, then you can use this method.

SEARCH() Function

Syntax

=SEARCH(find_text,within_text,[start_num])
  • Takes three inputs, find_text (can be a single value or an array),within_text, start_num.
  • Returns the position where the find_text input matches inside the within_text text.
  • Starts searching from the start_num position.

For example,

SEARCH("a","anahita",2) = 3

It is almost the same as the FIND() Function.

If you want to know more about the SEARCH() function, visit this link.

Now the formula for separating the names will be

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

Names separated in Excel

And the formula for separating the IDs will be

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

IDs separated in Excel

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


4. Using a Macro (VBA Code)

Now we will separate texts and numbers from a cell using a Macro (VBA Code)

  • 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.

VBA Toolbar Options

  • Click on Module. You will get a new Module window.

New Module window in Excel

  • 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

This site helped us 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 type “Excel Macro Enabled Worksheet”.
  • Now come back to your worksheet. In the Student Name column, insert the formula
=SplitText(B4,FALSE)

You will get the names separated.

Text separated by Macro in Excel

  • And in the Student ID column, insert the formula
=SplitText(B4,TRUE)

You will get the students IDs separated.

Numbers separated by Macro in Excel

 

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. Look at the images below.

A Macro in Excel.

Another Macro in Excel


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.


Further Readings

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo