convert to Numer
 

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

convert to Numer
 

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


"bijan" wrote:

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

convert to Numer
 

Thank you Sam,
It works perfect

"Sam Wilson" wrote:

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


"bijan" wrote:

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

convert to Numer
 

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.

--
Rick (MVP - Excel)


"bijan" wrote in message
...
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