Extracting numeric values from string

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
RJF wrote...
....
Problem: It does not work when numbers are not clustered together such as
scs987dtg1234
Click to expand...
....

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/microsoft.public.excel.worksheet.functions/msg/d2d252b4201d9d22
 
Back
Top