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


"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
>

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
> >

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" <(E-Mail Removed)> wrote in message
news:CD2C05EE-8983-4A51-9C32-(E-Mail Removed)...
> 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
>