Close

Not a member yet? Register now and get started.

lock and key

Sign in to your account.

Account Login

Forgot your password?

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.

___________________________

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:
Facebooktwittergoogle_plusredditpinterestlinkedin

Leave a Reply

Please confirm you are a person and not a 'bot' by answering this simple question: Time limit is exhausted. Please reload CAPTCHA.

  1. Video: SOLVED: How To Use Flash Fill in Excel to Separate Numbers From Text - Up & Running Technologies Calgary10-06-16