Sometimes you may have the text and numeric data in the same cell, and you may have a need to separate the text portion and the number portion in different cells.
While there is no inbuilt method to do this specifically, there are some Excel features and formulas you can use to get this done.
In this tutorial, I will show you 4 simple and easy ways to separate text and numbers in Excel.
Working with data in Excel often means dealing with a combination of numbers and text. While numbers are great for calculations and analysis text gives meaning and context. The challenge comes when you need to work with just the numbers or just the text separately. Fortunately, Excel provides some simple ways to separate numbers and text into different columns.
Why Separate Numbers and Text?
Here are some common reasons you may want to separate numbers and text in an Excel spreadsheet
-
To sort or filter the numbers and text separately. This allows you to work with just the numeric data for calculations or charts.
-
To format the numbers and text differently. You can apply numeric formats to the numbers and text formats like wrap text to the text columns.
-
To import or export just the numeric or text data. For example, you may want to analyze the numbers in another tool or export the text to a word processing program.
-
To use the text or numbers separately in formulas or pivot tables. It’s easier to reference a column of numbers vs a mixed column.
-
To split non-uniform data into uniform columns. If you have inconsistent data with numbers and text combined in cells, separating them can clean and organize the data.
Steps to Separate Text and Numbers
Excel provides a simple tool to separate numbers and text into different columns with just a few clicks. Here are the key steps:
1. Select the Column to Separate
- Select the column or data range that contains the combination of numbers and text you want to split apart.
2. Go to Data Tab and Click Text to Columns
- On the Data tab in the Data Tools group, click the Text to Columns button. This opens the Convert Text to Columns Wizard.
3. Choose Delimited and Click Next
- In Step 1 of the wizard, choose the Delimited radio button and click Next. Delimited means separating the text based on a delimiter between values.
4. Select the Space Delimiter
- In Step 2, select the Space delimiter option and click Next. This will split the text based on the spaces between words or numbers.
5. Format the Separated Data as Text or General
- In step 3, you can specify format types for the separated data. For the text, choose Text format. For the numbers, General or Number format tend to work best.
6. Place the Split Data into Separate Columns
- By default, the separated numbers and text will fill separate columns to the right of your original data. Click Finish to complete the split into columns.
And that’s it! Those few simple steps will separate your text and number data into organized columns for easier analysis and reporting.
Customize the Separator
The Text to Columns wizard can use other delimiters besides just spaces to separate your data. Some other useful options include:
- Commas – Split on the commas between values
- Tabs – Separate based on tab characters between text and numbers
- Other – Specify any custom delimiter like ; , / etc.
So if your data uses something besides spaces to separate text and numbers, choose the appropriate delimiter in the wizard.
Separate on Fixed Widths
If your data has fixed spacing instead of delimiters, you can also split by specific character widths. Here’s how:
-
Select Fixed width rather than Delimited in Step 1 of the wizard
-
Click on the vertical preview line to set width points between the columns
-
The data will split and fill columns at the specified width points
This takes a bit more work to set the exact split widths, but works great for data with rigid fixed spacing.
Split Using Excel Formulas
In addition to Text to Columns, you can also separate numbers and text using Excel formulas. This allows even more flexibility to customize how the split occurs.
Some useful formulas for splitting text include:
-
=LEFT(text, number_chars)– Returns leftmost characters from text -
=RIGHT(text, number_chars)– Returns rightmost characters from text -
=MID(text, start_num, number_chars)– Returns characters from the middle of text -
=SEARCH("find_text", within_text)– Finds position of find_text in the within_text -
=FIND("find_text", within_text)– Same as SEARCH, but case-sensitive
You can also split by specifying the character number position directly:
-
=LEFT(A2,3)returns the first 3 characters -
=RIGHT(A2,LEN(A2)-3)returns all but the first 3 characters
This takes more work, but allows very precise control over how to split text and numbers in Excel.
Tips for Improved Separations
Here are some useful tips for getting the best results when separating text and numbers in Excel:
-
Trim extra spaces from the original data using the TRIM function. This removes extra spaces between words or numbers.
-
Use proper data types when formatting (General for numbers, Text for text). This prevents issues with leading zeros, numbers stored as text, etc.
-
Adjust column widths to fit the separated data. Numbers need less width than full text strings.
-
Add helper columns with formulas like LEFT, RIGHT, etc to separate the data step-by-step if needed.
-
For more complex data, Power Query (Get & Transform) provides more advanced split capabilities.
-
Test and validate the split data, especially unusual corner cases in your dataset.
Following Excel best practices will help ensure you cleanly split text and numbers into their own organized columns.
Example of Separating Text and Numbers
To see it in action, here is a step-by-step example of separating a mix of text and numbers in Excel:
- Here is a dataset with product codes (text) and order quantities (numbers) combined in one column:
![Messy dataset][]
-
Select the data and go to Data > Text to Columns
-
Choose Delimited > Next, then Space > Next
-
Format the first column as Text and second as General
-
Click Finish to complete the split into two columns
-
The text and numbers are neatly separated for easier analysis and reporting!![Clean separated data][]
While this example is simplistic, it demonstrates the quick process for splitting text and numbers using Excel’s built-in tools.
When to Separate Data in Power Query Instead
The Text to Columns wizard works great for basic text and number separation tasks. However, for more complex datasets, the Power Query (Get & Transform) tools may work better.
Consider using Power Query to split columns if:
- You need to split on multiple or nested delimiters
- Your data has very inconsistent formats and spacing
- You want to save the separation steps for re-use on new data
- You have many columns to separate (over 100+)
- You want to automatically refresh the splits when your source data changes
Power Query provides more robust data transformation capabilities for complex text and number separation scenarios.
Separating Text and Numbers by Data Type
Separating text and numbers is really just splitting data by their underlying data type. Excel provides additional options to explicitly isolate specific data types:
- Convert to number – Changes non-numbers stored as text into numeric values
- Clean tool – Uses filters to remove non-numeric characters from text
- Advanced filter – Filter by number, text, date criteria to isolate data types in their own column
So in addition to splitting by delimiter, also consider using Excel’s built-in data cleaning tools. They provide another angle of attack for separating text, numbers, and dates.
Ideal Next Steps After Separating
Once you’ve successfully split your text and number data into separate columns, here are some beneficial next steps:
- Apply conditional formatting rules to the separated data. This can highlight anomalies like text in a numbers column.
- Add columns with formulas that reference just the number or just the text data.
- Pivot the separated data into a pivot table for new aggregation and analysis.
- Use the number data to create charts and graphs. Visualizations are easier when numbers are isolated.
- Export just the text or just the numbers to other systems or documents.
Separating your data is often just the first step. The individual columns of text and numbers then open up many new reporting and analysis options in Excel.
Whether you need to organize messy data or perform analysis on specific data types, separating numbers and text in Excel is easy using Text to Columns. With just a few clicks, you can split combined data into organized columns of numbers and text. This unlocks easier sorting, formatting, reporting, and analysis. So don’t shy away from datasets with a mix of data types – use Excel’s tools to effortlessly separate the numbers and text
Separate Text and Numbers Using Formula
Below I have the employee data in column A and I want to use a formula to extract only the text part and put it in column B and extract the number part and put it in column C.
Since the data is not consistent (i.e., the alphabets in the department code and the numbers in the employee number are not of the consistent length), I cannot use the LEFT or RIGHT function to extract only the text portion or only the number portion.
Below is the formula that will extract only the text portion from the left:
And below is the formula that will extract all the numbers from the right:
How does this formula work?
Let me first explain the formula that we use to separate the text part on the left.
The FIND part in the formula finds the position of digits 0 to 9 in cell A2. In case it finds that digit in cell A2, it returns the position of that digit, and in case it is not able to find that digit, then it returns the value error (#VALUE!)
For cell A2, it gives a result as shown below:
- For 0, it returns #VALUE! as it cannot find this digit in cell A2
- For 1, it returns 4 as that is the position of the first occurrence of 1 in cell A2
- and so on…
This FIND formula is then wrapped inside the IFERROR function, which removes all the value errors but leaves the numbers.
The result of it looks like as shown below:
The MIN function then goes through the above result and gives us the minimum value from the results. Since each number in the array represents the position of that corresponding number, the minimum value tells us where the numerical value starts in the cell.
Now that we know where the numerical values start, I have used the LEFT function to extract everything before this position (which would be all the text in the cell).
Similarly, you can use the same formula with a minor tweak to extract all the numbers after the text. To extract the numbers, where we know the starting position of the first digit, use the MID function to extract everything starting from that position.
And what if the situation is reversed – where we have the numbers first and the text later and we want to separate the numbers and text?
You can still use the same logic with one minor change – instead of finding the minimum value that gives us the position of the 1st digit in the cell, you need to use the MAX function To find the position of the last digit in this cell. Once you have it, you can again use the LEFT function or the MID function to separate the numbers and text.
Separate Text and Numbers Using Flash Fill
Below I have the employee data in column A, where the first few alphabets indicate the department of the employee and the numbers after it indicates the employee number.
From this data, I want to separate the text part and the number part and get these into two separate columns (columns B and C).
The first method that I want to show you to separate text and numbers in Excel is by using Flash Fill.
Flash Fill (introduced in Excel 2013) works by identifying patterns based on user input.
So if I manually enter the expected result in column B, Flash Fill will try and decipher the pattern and give me the result for all the cells in that column.
Below are the steps to separate the text part from the cell and get it in column B:
- Select cell B2
- Manually enter the expected result in cell B2, which would be MKT
- With cell B2 selected, place the cursor at the bottom right part of the selection. You’ll notice that the cursor changes into a plus icon (this is called Fill Handle)
- Hold the left mouse/trackpad key and drag the Fill Handle to fill the cells. Don’t worry if the cells are filled with the same text
- Click on the Auto Fill Options icon and then select the ‘Flash Fill’ option
The above steps would extract the text part from the cells in column A and give you the result in column B.
Note that in some cases, Flash Fill may not be able to identify the right pattern. In such cases, it would be best to enter the expected result in two or three cells, use the Fill Handle to fill the entire column, and then use Flash Fill on this data.
You can follow the same process to extract the numbers in column C. All you need to do is enter the expected result in cell C2 (step 2 in the process laid out above)
Note that the result you get from Flash Fill is static and would not update in case you change the original data in column A. If you want the result to be dynamic, you can use the formula method covered next.
How To Separate Numbers From Text In Excel || Excel Tips & Tricks || dptutorials
How to split numbers from text in Excel?
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?
How to extract numbers from text in Excel?
Here, each cell has a mix of text and numbers, with the number always appearing at the end of the text. In such cases, we will need to use a combination of nested Excel functions to extract the numbers. The functions we will use are: FIND – This function searches for a character or string in another string and returns its position.
How to separate a text string into two columns in Excel?
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. 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.
How do I separate text and numbers in Excel?
If the text and numbers are joined, uncheck all delimiters. Correctly identifying the delimiter is key. If your data isn’t separated by spaces, commas, or any other delimiter, you may need to use a different method. For the text column, select ‘Text’, and for the numbers, choose ‘General’. Then, click ‘Finish’ to execute the separation.
