Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extracting numbers from string | Excel Discussion (Misc queries) | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
Extracting Values on one list and not another | Excel Discussion (Misc queries) | |||
Leading Zeros in Numeric Values | Excel Worksheet Functions | |||
Convert Numeric into Text | Excel Worksheet Functions |