Remove alphabet from a string

[Edmund Seet]

May I know is there any function or any method that I can
remove ALPHABET characters leaving only NUMERIC value?
What I currently doing is that I been using formulars such
as MID, LEFT & RIGHT to remove the ALPHABET and special
characters. This method is indeed tedious. I am very
curious to know if you have any better method to recomend?

Example:
Invoice Number Result
IV3F02/49271 30249271
IES-30600001 30600001
C31946 31946

A macro would be the last resort.

TIA
Edmund Seet

 

[AndyB]

Edmund

This is one of the functions which is included in ASAP Utilities' add-in.
There are plenty of other useful functions too. For more info, visit
www.asap-utilities.com

HTH
Andy

 

[Ron Rosenfeld]

May I know is there any function or any method that I can
remove ALPHABET characters leaving only NUMERIC value?
What I currently doing is that I been using formulars such
as MID, LEFT & RIGHT to remove the ALPHABET and special
characters. This method is indeed tedious. I am very
curious to know if you have any better method to recomend?

Example:
Invoice Number Result
IV3F02/49271 30249271
IES-30600001 30600001
C31946 31946

A macro would be the last resort.

TIA
Edmund Seet

A macro is easier to implement. This one includes any decimal points as a
number. If that is not to your liking, merely delete line 8 which refers to
the decimal point.

=================
Option Explicit
Function GetValue(str)
Dim N As Integer, i As String
i = ""
For N = 1 To Len(str)
If IsNumeric(Mid(str, N, 1)) Then
i = i & Mid(str, N, 1)
If Mid(str, N + 1, 1) = "." Then i = i & "."
End If
Next
If i = "" Then
GetValue = i
Exit Function
End If
GetValue = CDbl(i)
End Function
====================


--ron

 

[Harlan Grove]

...
...

A macro is easier to implement. This one includes any decimal points as a
number. If that is not to your liking, merely delete line 8 which refers to
the decimal point.

...

If you're going to go the UDF route, you might as well make the UDF as general
as possible. Linking into VBScript's RegExp object is the most general way to
accomplish most single string text transformations. See

http://www.google.com/groups?selm=eLMja75S$GA.277@cppssbbsa05

Using the Subst UDF therein, the OP's problem could be solved with

=Subst(A1,"\D+","")

and your modification to include decimal points (which would include any and all
'.' characters in the string) could be solved with

=Subst(A1,"[^.0-9]+","")

or the first numeric substring (including negatives, scientific notation and
percentages, but not dates) could be extracted with the nastier

=Subst(A1,"(-[^0-9.]+|\.\D+|[^-0-9.]+)*(-?\d*\.?\d+(E[-+]?\d+|%)?).*","$2")

 

[Dana DeLouis]

This macro idea is slower than the others, but I thought I'd mention it ...

Function ExtractNumbers(s As String) As String
'// Keeps only digits
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "\D"
ExtractNumbers = .Replace(s, vbNullString)
End With
End Function


Sub TestIt()
Debug.Print ExtractNumbers("IV3F02/49271")
Debug.Print ExtractNumbers("IES-30600001")
Debug.Print ExtractNumbers("C31946")
End Sub

 

[Thomas]

Or another approach with a UDF:

Function Numerics(Str As String)
Dim i As Integer: Dim Num As String
For i = 1 To Len(Str)
If Mid(Str, i, 1) Like "[0-9]" Then
Num = Mid(Str, i, 1)
Else: Num = "": End If
Numerics = Val(Numerics & Num)
Next
End Function

 

[Harlan Grove]

Or another approach with a UDF:

Function Numerics(Str As String)
Dim i As Integer: Dim Num As String
For i = 1 To Len(Str)
If Mid(Str, i, 1) Like "[0-9]" Then
Num = Mid(Str, i, 1)
Else: Num = "": End If
Numerics = Val(Numerics & Num)
Next
End Function

...

Using Like would be faster than using VBScript RegExp objects, but you're
eliminating much of the efficiency gain by calling Val inside the loop rather
than building up the digit string inside the loop and converting it to a value
after the loop ends. Then again, if there were 16 or more decimal digits in the
string, you'd return a screwed up result if you convert it to a number. In this
case, much better to return a string.

Tangential: using colons as statement separators is a bad idea - makes for less
readable/maintainable code. The performance benefit that colons provided in
interpretted BASICA no longer exists in compiled versions of Basic.

 

[Ron Rosenfeld]

Linking into VBScript's RegExp object

You are undoubtedly correct. And you've posted information about this in the
past. But I don't know enough to use this yet.


--ron