Extracting numbers and text from inconsistent text/number formatted string

**Brandivil** · 01-25-2013, 11:52 AM

Hi,

I have large tables of numbers and text...the data tables contain different ranges of data as below

12HN - two numbers followed by two characters
732AD - three numbers followed by two characters
5PX - one number followed by two characters
3 - one number and no characters

Is there formula method that just extracts the number and another to just extract the text?

Been struggling using the below formula, it works until you get a '5PX' type format, instead of returning the number alone '5', it returns '5P'.

=IF(LEFT(I913,2)>9,LEFT(I913,2),IF(LEFT(I913,2)<9,LEFT(I913,1),0))

Many thanks

Brandivil

**Dunc3142** · 01-25-2013, 01:13 PM

This method uses a formula and assumes that the numbers are always first. I know that it is not VBA but I thought it might work for you. You could hide the columns that actually extract the numbers and just show the results.