SOLVED: Video: How to Separate Numbers From Letters In an Excel Text String

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.

UPDATE: Jan 18, 2017 – Ezekiel from New Mexico pointed out that the code was not translating properly from HTML and that there was a missing file.   Both of these issues have been corrected.

___________________________

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.