Extract Number with zero

[ltong]

Hi,

How to device an excel function or excel VBA to extract only the
number together with zero?, for the following : -

1) hgl5480,k00l92 =1548000192
2) mkg00152mn(a01,4) =00152014
3) OR00000254600 =00000254600
4) jdwoqp00p054v-gdw0014 =000540014
5) jkh4025lop05'0960 =4025050960

Thanks in advance

Regards
Lenard

 

[Tom Ogilvy]

Public Function ReturnNumerals(rng As Range) As String
Dim sStr As String, i As Long, sStr1 As String
Dim sChar As String
sStr = rng.Value
For i = 1 To Len(sStr)
sChar = Mid(sStr, i, 1)
If sChar Like "[0-9]" Then
sStr1 = sStr1 & sChar
End If
Next
Debug.Print sStr, sStr1
ReturnNumerals = sStr1
End Function


Put in a general module

 

[tkstock]

Function myNumber(st as String) as String
dim st2
for i = 1 to len(st)
if isnumeric(mid(st,i,1)) then st2 = st2 & mid(st,i,1)
next i
myNumber = st2
End Function


To

 

[Tom Ogilvy]

comment out or take out the debug print:

Public Function ReturnNumerals(rng As Range) As String
Dim sStr As String, i As Long, sStr1 As String
Dim sChar As String
sStr = rng.Value
For i = 1 To Len(sStr)
sChar = Mid(sStr, i, 1)
If sChar Like "[0-9]" Then
sStr1 = sStr1 & sChar
End If
Next
' Debug.Print sStr, sStr1
ReturnNumerals = sStr1
End Function

--
Regards,
Tom Ogilvy

Public Function ReturnNumerals(rng As Range) As String
Dim sStr As String, i As Long, sStr1 As String
Dim sChar As String
sStr = rng.Value
For i = 1 To Len(sStr)
sChar = Mid(sStr, i, 1)
If sChar Like "[0-9]" Then
sStr1 = sStr1 & sChar
End If
Next
Debug.Print sStr, sStr1
ReturnNumerals = sStr1
End Function


Put in a general module

--
Regards,
Tom Ogilvy



--
Regards,
Tom Ogilvy

Hi,

How to device an excel function or excel VBA to extract only the
number together with zero?, for the following : -

1) hgl5480,k00l92 =1548000192
2) mkg00152mn(a01,4) =00152014
3) OR00000254600 =00000254600
4) jdwoqp00p054v-gdw0014 =000540014
5) jkh4025lop05'0960 =4025050960

Thanks in advance

Regards
Lenard