- Excel 365
- Excel 2021
- Excel 2019
- Excel 2016
- February 12, 2022
- 12 Comments
You’ll sometimes encounter a situation where a cell contains both text and numbers, often when data has been imported from another system.
In this example, you want to separate the names and numbers from column A into columns B and C. There are three ways that you could do this.
Option 1: Flash Fill
Flash Fill was a feature introduced in Excel 2013. If you have an older version or a Mac version you won’t be able to use it.
Flash Fill makes this extremely easy, and it’s almost always the best choice for tasks like this.
To use Flash Fill you first need to provide it with an example of what you are trying to do, as shown above. Once you’ve provided an example, click:
Home > Editing > Fill > Flash Fill
You can also use the shortcut key <Ctrl>+<E> to do this.
Flash Fill looks at your example, figures out what you are trying to do, and automatically extracts all of the other names.
You can repeat the same process for column C and all of the names and numbers are extracted!
Flash Fill is explained in more depth in our Essential Skills course, along with some more advanced Flash Fill techniques.
Flash Fill offers the simplest and easiest solution, but it needs to be done manually each time you need to split the names and won’t be available to you if you are using a Mac version of Excel or an older version of Excel for Windows.
Option 2: Text to Columns
If you’re an experienced Excel user, the Text to Columns feature might also have come to mind. Text to Columns allows you to split cells like this, but only if they are separated by a consistent character or a fixed width.
In this case, Text to Columns could be used to split the data using a space as the delimiter. This would result in 3 columns rather than the two you want, so you would need to then concatenate the names back together.
You can access the Text to Columns tool by clicking: Data > Data Tools > Text to Columns
Using Text to Columns is covered in depth in our Expert Skills course.
Option 3: Using Formulas
The previous options only offer a ‘one-off’ solution. If the data in column A came from an external data source, you wouldn’t want to have to re-run Flash Fill or Text to Columns every time you refreshed the data.
It’s possible to extract the names and numbers using formulas, but you’ll need some very complex formulas to do this.
Finding the position of the first number
The first thing your formula will need to do is find the position of the first number in the cell. The formula to do this is:
=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&”0123456789″))
This looks daunting, but it’s not actually that hard to understand if you understand the FIND function. FIND is covered in depth in our Expert Skills course.
The {0,1,2,3,4,5,6,7,8,9} numbers in curly brackets might also look confusing. This is called an ‘array’. What is means is that the FIND function will run 10 times, once for each of the numbers. The MIN function will then return the lowest value that is returned (i.e. the position of the first number in the cell).
The reason you’re concatenating the text “0123456789” is because the FIND function will return an error if it doesn’t find what it’s searching for, and this will prevent the MIN function from being able to return a result. To make sure that FIND can always return a valid result each time it runs, you append all of the numbers to the end of the text it is searching.
Extracting the names
Now that you have the position of the first number, you can use the LEFT function to extract the names like this:
=LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&”0123456789″))-1)
The LEFT function is explained in depth in our Expert Skills course.
Extracting the numbers
In a very similar way, the numbers can be extracted using the RIGHT and LEN functions like this:
=RIGHT(A2,LEN(A2)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&”0123456789″))+1)
As with LEFT, the RIGHT and LEN functions are covered in our Expert Skills course.
The Result
After filling down the formulas you can see that they have successfully extracted the names and numbers. These formulas will work in any version of Excel, and will continue to work even if the data in column A changes.
You can download a copy of the workbook showing the formulas in action.
These are the only up-to-date Excel books currently published and includes the new Dynamic Arrays features.
They are also the only books that will teach you absolutely every Excel skill including Power Pivot, OLAP and DAX.
Some of the things you will learn
12 Responses
Based on the above advice, it cannot separate text and numbers when numbers are in front of the text, e.g. 12345abcde. Any further advice?
Hi Alex,
All of the above solutions should work with the numbers in front of the text.
For the formula-based approach the difference would be that you would need to search for the last number rather than the first. You could do this by using the MAX function instead of the MIN function.
Once you have the location of the last number you should be able to use the LEFT function to extract all of the numbers.
ACTUALLY THIS IS NOT WORKING FOR ME.
If you can be more specific about what you are trying to do and how it is not working I should be able to offer some advice.
After copying the formula for finding the first numeral, edit the double quote marks in the copy, replacing each of them with your keyboard’s double quote key.
This is a common problem on websites: the articles are written with word processing software which will replace the keyboard typed double quotes with slicker ones (in Word this is called using smart quotes) meant for publishing. And if that does not happen, or is correctly fixed afterward, any processing when loading to the webiste may do it also.
I also have a situation where I need to separate numbers and text in the same cell, where the numbers are in front. I tried using the MAX function and it’s not working for me. What formula could I use to create the result below?
Before: 00868#09ALVARADO
After: 00868#09 ALAVARADO <—There's a space between "9" and "A"
If the two codes have a consistent length you should be able to do this easily using Flash Fill or by using Text To Columns, as mentioned above. If you need a formula-based solution and the codes are always the same length you should also be able to do this easily using the LEFT and RIGHT functions. It’s only in cases where the codes could have a variable length that you will need a formula that searches for the position of the last number or first letter of the code.
The MAX solution will only work correctly if all of the numbers 0-9 can be found in the target, so it might not actually be the best approach in a situation like this. Formulas do exist that can reliably extract the last number from a value without any foreknowledge of what numbers might be present, but they are much more complex.
A simpler solution in this case would be to find the first letter instead of the last number. Assuming the value is in cell A2, you could do that using:
=MIN(FIND({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”J”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”},A2&”ABCDEFGHIJKLMNOPQRSTUVWXYZ”))-1
This works in exactly the same way as the formula above, but it runs the FIND function 26 times (once for each letter of the alphabet) and returns the lowest result. You can then extract the values on each side using the LEFT, RIGHT and LEN functions.
To get the part before the A you would use:
=LEFT(A2,MIN(FIND({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”J”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”},A2&”ABCDEFGHIJKLMNOPQRSTUVWXYZ”))-1)
To get the part after the A you would use:
=RIGHT(A2,LEN(A2)-(MIN(FIND({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”J”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”},A2&”ABCDEFGHIJKLMNOPQRSTUVWXYZ”)))-1)
Now that you have extracted both parts of the value, you could join them back together with a space between them using the & operator, for example:
=B2&” “&C2
Could you please help to find highest value and lowest value from this
please paste the below data in one cell (Ex. A1) and need to find highest value
Cell: A1
a)63Ra b)64Ra c)65Ra d)62Ra e)61Ra f)63Ra g)60Ra h)62Ra
Hi Adaikkala,
I would recommend first breaking each of the items into separate cells, either using Flash Fill or the Text to Columns feature. You can see how to use Flash Fill above, and Text to Columns is covered in this article.
Both skills are covered in depth in our courses.
After the values have been placed in separate cells you should be able to use the skills shown above to extract the numbers and could then use the MAX and MIN functions to determine the highest and lowest.
Thank you very much
=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&”0123456789″))
does nothing for me
i tried replacing ” and ; etc even typed it by hand new and it does not work.
the line always just says
=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&”0123456789″))
instead of showing the result.
Any thoughts how to fix this?
I’ve just tested this and it worked perfectly for me. With the text “George Barnes 197.2″ in cell A2, the formula: =MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&”0123456789”)) returned 15 as expected. If you copy and paste from the web page you’ll have to manually re-enter the double quotation marks (web page quotes are different to Excel quotes) but you’ve said that you’ve done that.
My best guess is that it is an Excel version issue. There have been a huge number of Excel versions and some have bugs that others don’t have, some also have functions that others don’t have. All I can report is that it worked perfectly using Excel 365 version 2103 Build 13901.20462 which was the latest version of Excel at time of writing. Having said that, I’d also expect it to work on all other versions of Excel but your experience suggests otherwise!