Multiple Substitute / Extract Numbers from Alphanumeric

[davidlim]

hi guys,


this really bothers me hope u can help.


A1 contains (8017) 342-3378, and only contain 4 non-numbers --> (, ), -, <space>


my original intention was to extract the numbers from the string above. i.e. 80173423378


using purely formula, i have not seen any that works.


looking at the string i noticed there are only 4 fixed non-numbers. so i was thinking, instead of writing long formula to extract the numbers only, i might as well replace/substitute 4 non-numbers into <space> and use TRIM.


i tried this formula: =TRIM(SUBSTITUTE(A1,{"(",")","-"},""))


but it seems the hard-coded array doesnt work. the only working solution is to nest SUBSTITUTE 4 times (it works but not practical if you have multiple characters to replace).


any ideas?


thanks!

 

[Luke M]

Not sure if there's a shorter way to do it natively in XL, but here's a short UDF that might work as well.

[pre]

Function ReturnNumbers(s As String)
Dim xChar As String
Application.Volatile
For i = 1 To Len(s)
xChar = Mid(s, i, 1)
If "123456789" Like "*" & xChar & "*" Then
ReturnNumbers = ReturnNumbers & xChar
End If
Next
ReturnNumbers = ReturnNumbers * 1
End Function

[/pre]
Note that if the first number is a 0, it won't get displayed (unless the cell formatting forces leading zeros).

 

[NARAYANK991]

Hi David ,


If you are sure that the non-numeric characters will always be in the same "slots" , so to say , then instead of using SUBSTITUTE , you can use REPLACE.


Narayan

 

[NARAYANK991]

Hi ,


There's an amazing formula at this link ; it works !


http://www.pcreview.co.uk/forums/strip-non-numeric-characters-cell-t3787866.html


Narayan

 

[davidlim]

thanks Luke for the shorter UDF. it seems that the UDF strips out 0 from the number string. I modified slightly so that it retain 0. Of course leading 0 is not shown unless formatted.


NARAYANK991, the "slots" are mostly the same, which is why i was looking for a shorter, more direct formula/function


thanks all for ur help!