Excel - Extracting Digits From Text

Asked By Faraz A. Qureshi

09-Feb-10 07:55 AM

What could be wrong with the following piece of code for a UDF?

Function NFW(RNG As Range)
Dim RSLT As String
RSLT = ""
For X = 1 To Len(RNG)
If Asc(Mid(RNG.Text, X, 1).Value) > 47 And _
Asc(Mid(RNG.Text, X, 1).Value) < 58 Then
RSLT = RSLT & Mid(RNG.Text, X, 1)
End If
Next
NFW = RSLT
End Function

--
Thanx in advance,
Best Regards,

Faraz

09-Feb-10 08:11 AM

Hi,

Try it like this

Function nfw(rng As Range)
Dim RSLT As String
RSLT = ""
For X = 1 To Len(rng.Value)
If Asc(Mid(rng.Value, X, 1)) > 47 _
And Asc(Mid(rng.Value, X, 1)) < 58 Then
RSLT = RSLT & Mid(rng.Value, X, 1)
End If
Next
nfw = RSLT
End Function

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

09-Feb-10 08:29 AM

Hi Faraz

---In your code .Value should be removed as below

Function NFW(RNG As Range)
Dim RSLT As String
RSLT = ""
For x = 1 To Len(RNG)
If Asc(Mid(RNG.Text, x, 1)) > 47 And _
Asc(Mid(RNG.Text, x, 1)) < 58 Then
RSLT = RSLT & Mid(RNG.Text, x, 1)
End If
Next
NFW = RSLT
End Function

--OR to make it short

Function NFW1(RNG As Range) As String
Dim intX As Integer
For intX = 1 To Len(RNG)
If IsNumeric(Mid(RNG, intX, 1)) Then NFW1 = NFW1 & Mid(RNG, intX, 1)
Next
End Function

--
Jacob

09-Feb-10 08:44 AM

In addition to the advice already given it'd be more efficient to read the
value of the range to a string variable first rather than reading the range
in each loop.

FWIW if you have very long strings there are faster ways without using the
various string functions like Asc and Mid.

Other things to consider
- maybe declare the input argument As Variant,
- how to cater for the input of a multi-cell range
- is a potential decimal separator relevant.

Regards,
Peter T

09-Feb-10 12:58 PM

Another addition to the mix.

Function RemAlpha(str As String) As String
'Remove Alphas from a string
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\D"
RemAlpha = re.Replace(str, "")
End Function


Gord Dibben  MS Excel MVP

09-Feb-10 02:00 PM

Yeap, be mindful of the .Value and the .Text.

This is another way to do what you want to do:
Function numit2(r As Range) As String
Dim s As String, s2 As String, c As String
s2 = ""
s = r.Value
l = Len(s)
For i = 1 To l
c = Mid(s, i, 1)
If c Like "#" Then
s2 = s2 & c
End If
Next
numit2 = s2
End Function

Public Function stripNumbers(rng As Range)
Dim i As Integer
For i = 1 To Len(rng.Value)
If Mid(rng.Value, i, 1) >= "0" And Mid(rng.Value, i, 1) <= "9" Then
strNum = strNum & Mid(rng.Value, i, 1)
End If
Next
stripNumbers = CDbl(strNum)
End Function




--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

09-Feb-10 08:07 PM

One more method to consider...

Function RemoveNonDigits(ByVal StrIn As String) As String
Dim X As Long
For X = 1 To Len(StrIn)
If Not Mid(StrIn, X, 1) Like "#" Then Mid(StrIn, X, 1) = " "
Next
RemoveNonDigits = Replace(StrIn, " ", "")
End Function

--
Rick (MVP - Excel)

09-Feb-10 11:56 PM

WOW!!!
Sure am lucky 2 have a collection of priceless friends like u all!!!
Thanx guys!
--
Thanx in advance,
Best Regards,

Faraz