alphanumeric to numeric

[bubba100]

I have been given an excel workbook with one column that "should be" numeric only but..
It has A100, A5000, 1 and 400. They should be 100, 5000,1 and 400.
According to the Microsoft online help the formula "should" convert to all numeric "=1*MID(C2,MATCH(TRUE,ISNUMBER(1*MID(C2,ROW($1:$9),1)),0),COUNT(1*MID(C2,ROW($1:$9),1)))" but what it returns is #NA.
Any ideas of where to look?
Thanks in advance.

 

[LNBruno]

You have to enter the formula as an array by pressing CTRL+SHIFT+ENTER.


< M!ke >
Acupuncture Development: a jab well done.

 

[yogia]

Hi bubba100:

If you know that there could be be only 1 alpha character preceding your number of interest, then you can simply use ...

=IF(CODE(LEFT(UPPER(A1)))>64,MID(A1,2,255),A1)+0

If the number of alpha characters preceding your number of interest could be more than 1, then you may want to try the following array formula to be entered with CTRL+SHIFT+ENTER rather than with just ENTER ...

=MID(A1,MATCH(TRUE,CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<59,0),255)+0

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[KenWright]

And if it is only an A that some of these numbers ere preceded by then just the following should do you:-

=--SUBSTITUTE(A1,"A","")

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[bubba100]

Sometimes I let the trees get in the way. Thanks.