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.

Leave a Comment

Your email address will not be published.