convert to Numer

  • Thread starter Thread starter bijan
  • Start date Start date
B

bijan

Hi all,
I need a function or sample vba code to convert data with character and
spetial character in column A To Culumn B with just Number:

Column A Column B
-------------------------- --------------------------
ZB0056/87998234/BCS 005687998234
64544/64646646BCS 6454464646646
LM01/636336321 01636336321
83333/77/393939 8333377393939

Thanks in advanced
Bijan
 
Public Function numbers(ByVal str As String) As String

Dim i As Integer
Dim s As String

For i = 1 To Len(str)
s = Mid(str, i, 1)
If IsNumeric(s) Then numbers = numbers & s
Next i

End Function
 
Thank you Sam,
It works perfect

Sam Wilson said:
Public Function numbers(ByVal str As String) As String

Dim i As Integer
Dim s As String

For i = 1 To Len(str)
s = Mid(str, i, 1)
If IsNumeric(s) Then numbers = numbers & s
Next i

End Function
Click to expand...
 
If your resulting number will never have more than 14 digits in it, and if
the text string your digits are embedded in will always be less than 300
characters total, then you can use this formula originally posted by
Lars-Ã…ke Aspelin...

=MID(SUMPRODUCT(--MID("01"&A1,SMALL((ROW($1:$300)-1)*ISNUMBER(-MID("01"&A1,ROW($1:$300),1)),ROW($1:$300))+1,1),10^(300-ROW($1:$300))),2,300)

This is an array formula and has to be confirmed with CTRL+SHIFT+ENTER
rather than just ENTER.
 
Back
Top