UPDATE: Oct 6 2016 – Before you spend a lot of time on creating a formula to separate text from numbers in Excel, watch our two minute video on how to use Excel’s little used but totally awesome FLASH FILL function to do this work for you.

___________________________

If you have a column of data that contains numbers and letters together and you need to separate them, it is easy to do using the traditional functions like RIGHT() and LEFT() as long as there is a delimiter of some sort… but what if there isn’t.

For instance, if you have the following string:

1234abc

2222def

3311hij

you can separate them using:

=LEFT(A1,4)

and

=RIGHT(A1,(LEN(A1)-4))

but what if you have something more complex that has no defined pattern other than a random number of numbers first and then some letters like:

1234abc

56d

789012345678efghijklmn

999ILikeCheese

777SouthWest

Well, now ‘you gots a problem!’. Fortunately you can use the following formulas to separate these into their own happy columns of only numbers and only letters:

=LEFT(A1,MATCH(TRUE,ISERROR(VALUE(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1))),0)-1)

and

=RIGHT(A1,(LEN(A1)-((MATCH(TRUE,ISERROR(VALUE(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1))),0))))+1)

CRITICAL NOTICE:You MUST paste this formula in to the Excel Formula Bar and BEFORE you click out of it, press CNTL+SHIFT+ENTER and that will make it an “array” which will be shown with French brackets (curly brackets {} ) around it

You will notice that they still use the same LEFT() and RIGHT() functions but the meaty formula in the middle is basically looking at each character and evaluating it as a number. If that fails (i.e. ISERROR) then it knows it has hit a number. BRILIANT! I wish I had come up with it but THIS guy came up with the core function.

We produced this short 4 minute video to demonstrate its use and you can download the sample Excel file I built for the video, which includes these formulas directly from us HERE.

Share This With Your Friends Now: