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