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

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.


"Faraz A. Qureshi" wrote:

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

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


"Faraz A. Qureshi" wrote:

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

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


"Faraz A. Qureshi" wrote in
message ...
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

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

On Tue, 9 Feb 2010 04:55:01 -0800, Faraz A. Qureshi
wrote:

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

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


"Gord Dibben" wrote:

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

On Tue, 9 Feb 2010 04:55:01 -0800, Faraz A. Qureshi
wrote:

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

.

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)


"Faraz A. Qureshi" wrote in
message ...
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

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

Faraz


"Faraz A. Qureshi" wrote:

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