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.

I’m trying to get the same results by my source text string has alpha characters first. Can this array be tweaked to work?

You should be able to easily modify that formula to work with text first but I am out of time (traveling) perhaps someone reading this thread can assist.

AFTER I SEPARATE THE NUMBERS FROM THE LETTERS IN THE STRING I WANT TO AUTOSUM THE COLUMN OF NUMBERS BUT I JUST GET 0. IS THERE A WAY TO ADD UP THE NUMBERS???

Have you tried using the FLASH FILL function as mentioned in the first line of the post. It should work. Alternately, you could copy the column in question and then PASTE SPECIAL > VALUES into a new column. That will allow you to sum or apply any other formula to it. Have a great day,