Extracting numeric values from string

My colleague has a problem where he needs to Extract numbers from
alphanumberic strings. Here is the formula he is using:
=MID(B7,MATCH(TRUE,ISNUMBER(1*MID(B7,ROW($1:$297),1)),0),COUNT(1*MID(B7,ROW($1:$297),1)))

It works if alphabetic and numbers are clustered together such as scs987
It works if alphabetic and numbers are not clustered together such as
scs987dtg

Problem: It does not work when numbers are not clustered together such as
scs987dtg1234

Any suggestions would be helpful.

Thank you.

You'd need VBA for that

http://tinyurl.com/53p5b


Regards,

Peo Sjoblom

"RJF" wrote:

> My colleague has a problem where he needs to Extract numbers from
> alphanumberic strings. Here is the formula he is using:
> =MID(B7,MATCH(TRUE,ISNUMBER(1*MID(B7,ROW($1:$297),1)),0),COUNT(1*MID(B7,ROW($1:$297),1)))
>
> It works if alphabetic and numbers are clustered together such as scs987
> It works if alphabetic and numbers are not clustered together such as
> scs987dtg
>
> Problem: It does not work when numbers are not clustered together such as
> scs987dtg1234
>
> Any suggestions would be helpful.
>
> Thank you.

We actually did find some VBA code that does the trick. It's just that in
this particular case, we really, really want to use a formula.

Thank you.

"Peo Sjoblom" wrote:

> You'd need VBA for that
>
> http://tinyurl.com/53p5b
>
>
> Regards,
>
> Peo Sjoblom
>
> "RJF" wrote:
>
> > My colleague has a problem where he needs to Extract numbers from
> > alphanumberic strings. Here is the formula he is using:
> > =MID(B7,MATCH(TRUE,ISNUMBER(1*MID(B7,ROW($1:$297),1)),0),COUNT(1*MID(B7,ROW($1:$297),1)))
> >
> > It works if alphabetic and numbers are clustered together such as scs987
> > It works if alphabetic and numbers are not clustered together such as
> > scs987dtg
> >
> > Problem: It does not work when numbers are not clustered together such as
> > scs987dtg1234
> >
> > Any suggestions would be helpful.
> >
> > Thank you.

RJF wrote...
....
>Problem: It does not work when numbers are not clustered together
such as
>scs987dtg1234
....

Do you want the result for this particular string to be 9871234? If so,
you have two choices: brute force and VBA. The brute force formula
looks something like

=IF(ISNUMBER(--MID(A1,1,1)),MID(A1,1,1),"")&IF(ISNUMBER(--MID(A1,2,1)),MID(A1,2,1),"")
&IF(ISNUMBER(--MID(A1,3,1)),MID(A1,3,1),"")&IF(ISNUMBER(--MID(A1,4,1)),MID(A1,4,1),"")
&IF(ISNUMBER(--MID(A1,5,1)),MID(A1,5,1),"")&IF(ISNUMBER(--MID(A1,6,1)),MID(A1,6,1),"")
&IF(ISNUMBER(--MID(A1,7,1)),MID(A1,7,1),"")&IF(ISNUMBER(--MID(A1,8,1)),MID(A1,8,1),"")
&IF(ISNUMBER(--MID(A1,9,1)),MID(A1,9,1),"")&IF(ISNUMBER(--MID(A1,10,1)),MID(A1,10,1),"")
&IF(ISNUMBER(--MID(A1,11,1)),MID(A1,11,1),"")&IF(ISNUMBER(--MID(A1,12,1)),MID(A1,12,1),"")
&IF(ISNUMBER(--MID(A1,13,1)),MID(A1,13,1),"")&IF(ISNUMBER(--MID(A1,14,1)),MID(A1,14,1),"")
&IF(ISNUMBER(--MID(A1,15,1)),MID(A1,15,1),"")&IF(ISNUMBER(--MID(A1,16,1)),MID(A1,16,1),"")
&IF(ISNUMBER(--MID(A1,17,1)),MID(A1,17,1),"")&IF(ISNUMBER(--MID(A1,18,1)),MID(A1,18,1),"")
&IF(ISNUMBER(--MID(A1,19,1)),MID(A1,19,1),"")&IF(ISNUMBER(--MID(A1,20,1)),MID(A1,20,1),"")

The VBA approach would involve something like

http://groups-beta.google.com/group/...d252b4201d9d22

Try this formula:

Formula:

Please Login or Register  to view this content.

I found it on an old [Solved] thread addressing exactly the same question. I do not even remember if it was on this forum or not. I just know that it works provided you do not need to retain leading zeros and the resulting number is not greater than 15 digits.....it starts rounding after that.

Hope this one helps.

Thank you (blush).

hats off to FlameRetired

regards