Happy Friday Excellers time for more #formulafriday fun. Today let’s look at how to split a cell that contains both text and numbers.
I received data this week that had both a product description and product code in the one cell and I wanted to split the contents into two seperate cells, one with the product description and one with the product code.
The first solution I looked at to use was the Text To Columns feature, but as the the product description is different string lengths I could not use the Fixed Width feature.
Time to turn to a Formula to help us out!!. Well it is actually a few formulas functions and it looks like this.
First we need to find the position of the first number in our cell. We do this using the following formula:-
Now we have the position of the first number ( in positon 29) we can extract all of the numbers after that using the formula below
This formula counts the number of characters that the first number Excel comes across, in our first cell this is 29. We can use the RIGHT function to extract the characters after that position and return our numbers.
Note the -5 as we have the 500ml being five extra characters which we need to disregard from the calculation as Excel has correctly counted the first position of a number at 29 as it came across 5.
Have you used this method before?, Would you prefer to use the Flash Fill Method?
Note for Excel 2013 onwards users.
We can also use the Flash Fill feature which is waaaay quicker to acheive the same result but only in those versions of Excel where it is available.
All you need to do is take the part of the data you want from the original cell, which is the Product Code paste or type into a new column, then drag the data down your cell until the Flash Fill icon appears, then hit flash fill.
When you release with cursor Excel will recognise the pattern of data in the data and fill the rest of the cells for you.
Somewhat quicker than the formula, but the formula is a good option if you are using earlier versions of Excel than 2013.
Dont forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.
- Create A Timestamp In Excel
- Hide all of your Excel formulas from prying eyes!
- Undertand and debug Excel formula errors
- How To Display a Plus + Sign On Positive Numbers With Custom Formatting In Excel