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 ID*s 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**

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 f****rom 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"))`

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

**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
s, the formula will be:*Student ID*

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

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 ID*s 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, **t****he LEFT function** takes two arguments: text, and position number. Returns the leftmost part of the text up to the given position number. **T****he 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)`

- 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
*ID*s will be:

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

- 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 ID*s separated.

*Student ID*s 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**

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

Really Usefull, Thank you

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

Greetings 3ADDOULA MA5ASSAK, thank you for your question. I hope the following codes will solve your issue.

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.