If you use Excel regularly, you may be familiar with its wide selection of different features to help you. The program’s unique ability to work with numbers and text makes it ideal for data manipulation. One function that users may be unaware of but can find helpful is the ability to separate text and numbers.
The process required to separate text and numbers in Excel is actually quite simple. All you need to do is input a couple of results for the algorithm to pick up, then click on the “Flash Fill” option, which will automatically do the rest. For example, if you have a column of mixed numbers and text, add the text of one or two results in the column next to it.
The algorithm will automatically pick up this information, and when you click on the option for “Flash Fill,” Excel will fill the entire column with just the text from the previous column. You can do the same thing with numbers, and at the end of the process, you will have a complete data set with the numbers and text separated.
- Firstly, open your Excel document.
- Select the column next to the number and letter mixed column.
- Now type out just the letters from the number and letter mixed column.
- Do the same for a couple more rows.
- Now click on the drop-down next to “Fill.”
- Complete the process by clicking “Flash fill.”
Upon completing the process, you will have a complete data set of just the numbers and text in two separate columns. You will, of course, also have the original mixed number and text column available for either reference, or if that information is still required, you can use it.
The “Flash fill” tool is a fantastic way to quickly use the Excel algorithm to fill large sections of repetitive or pattern data. In this case, we only want the text in one column and numbers in the other, so it is easy for the “Flash fill” to pick up this information from the results we typed out and then give us the complete data set.
No additional formulas or other restrictions could prevent you from copying the number or text or editing it as you require. The information you added using the “Flash fill” method is simply the text and numbers that Excel added.
An in-depth guide on how to separate numbers from text in Excel
If you are struggling to understand the process outlined above, you can use the in-depth guide to help. The in-depth guide will include screenshots allowing you to navigate the Microsoft Excel User Interface (UI). Microsoft Excel may include features that may be hard to understand properly; you can use the information we have provided in the in-depth guide to help go through the UI. I also highly recommend you use some in-depth analysis provided with more complex steps so you can go through the process without issues.
- Open your Excel document.
- Select the column next to the number and letter mixed column.
- In this example, the text and number mixed column is in A1, and the next available column is B1. So for this process, I will need to select column B1 to proceed with the steps ahead.
- Now type out just the letters from the number and letter mixed column.
- Now click on the drop-down next to “Fill.”
- Complete the process by clicking “Flash fill.”
Note: in the web version of Excel, go to “Data” at the top, select B1 (see image below) then click “Flash Fill” in the ribbon.
Microsoft Excel will automatically fill out all the information that matches your typed data. For instance, I typed the text from column A into column B, with just a few results. Now with that information available to the Excel algorithm, the flash fill technique will work without any issues.
- Type only the numbers into column C and flash fill to produce the numbers from column A.
If you find the flash fill option is not working, you must type out one or two more results until the algorithm picks up this information. However, I have found that after just one or two results, the “Flash fill” technique works without any issues. The process is similar to the numbers column, and at the end of the process, you will have a full data set with a column of text and a column of numbers separated from the original column.
Separate numbers from text in Excel using formulas
If Flash Fill isn’t working as required, you can try using the following formulas to extract the text and numbers from a mixed column into separate columns.
Note: in the formulas, the mixed text and number string is assumed to be located in column A. If the strings are located in a different column, change all occurrences of “A1” in the formulas to the column and row where your mixed string first occurs.
Also, the formulas assume the string is 1-100 characters long. If your string is longer, change all occurrences of “100” in the formula to the max length of your string.
- First, copy and paste the following formula into cell B1 (or paste into the column where you want the text to appear).
=CONCAT(IF(ISNUMBER(--MID(A1,ROW($1:$100),1)),"",MID(A1,ROW($1:$100),1)))- Then copy and paste the following formula into cell C1 (or paste into the column where you want the numbers to appear).
=CONCAT(IF(ISNUMBER(--MID(A1,ROW($1:$100),1)),MID(A1,ROW($1:$100),1),""))- Now select the cells containing the formulas, hover over the bottom right corner of the cell until the thin
plus icon appears, then click and drag the formula down.
Why may you want to separate numbers from text in Excel?
You may want to separate text or numbers from a column because the information needs to be separated into two individual data fields. For instance, if you have the house number and address all in one column, but you require the address to be on a separate column and the house number to be in another column, you can use the method above to help. You can import this as a CSV or save it the way it is. This will then help you to better manage the information to use at a later date.
Conclusion
Thank you for reading our content on separating numbers and text from a column in Excel. I have given you the steps to separate numbers and text from a column in Excel and use the “Flash fill” method to ensure completion in the easiest way possible. I have also given you some further information on why you may want to separate text and numbers from a column in Excel. If you require further assistance with the steps provided, drop a comment below, and we will address the situation.