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.

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

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

`=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.

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

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

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

## Separate Text and Numbers: Knowledge Hub

**<< Go Back to Split | Learn Excel**

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.