Separating text and numbers in Excel

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, but you can also read about it in this article.

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.

8 thoughts on “Separating text and numbers in Excel”

  1. 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?

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

      1. NotGreatAtExcel

        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"

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

  2. Adaikkala Regis P

    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

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

Leave a Comment

Your email address will not be published.