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.
5 Comments
David A. Flores · October 15, 2018 at 8:06 am
I’m trying to get the same results by my source text string has alpha characters first. Can this array be tweaked to work?
Ian Matthews · December 11, 2018 at 7:04 pm
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.
VALERIE · February 24, 2017 at 12:54 pm
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???
Ian Matthews · March 20, 2017 at 8:31 pm
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,
Video: SOLVED: How To Use Flash Fill in Excel to Separate Numbers From Text - Up & Running Technologies Calgary · October 6, 2016 at 7:40 pm
[…] you have more complex issues trying to separate numbers from text in Excel columns, our explanation of how to make this happen using a formula might be just the trick you were looking […]