VBA edit to return Null

[Nemo74]

Hello everyone,

I learned this code set up from the interwebs, but I'm not slick enough to modify it to return the word "Null" when there are no numbers in the target... if someone could lead me to water, I'm more than happy to learn how to drink.

Thanks,

Function PullNums(target As Range)Dim MyChar As String, i As Integer, MyChar2 As String


    MyChar = ""
    If Len(target.Value) = 0 Then GoTo GoExit
    For i = 1 To Len(target.Value)
        If IsNumeric(Mid(target, i, 1)) Then MyChar = MyChar & Mid(target, i, 1)
    Next i
GoExit:
    PullNums = MyChar
End Function

 

[dreid1011]

Perhaps something along the lines of:

If Len(MyChar) = 0 Then MyChar = "Null"

And put that between "Next I" and "GoExit".

 

[Rick Rothstein]

Here is the code you posted modified to do what you asked for...

[table="width: 500"]
[tr]
	[td]Function PullNums(target As Range)
    Dim MyChar As String, i As Integer, MyChar2 As String
    MyChar = ""
    If Len(target.Value) = 0 Then GoTo GoExit
    For i = 1 To Len(target.Value)
        If IsNumeric(Mid(target, i, 1)) Then MyChar = MyChar & Mid(target, i, 1)
    Next i
GoExit:
    If Len(MyChar) Then
      PullNums = MyChar
    Else
      PullNums = "Null"
    End If
End Function[/td]
[/tr]
[/table]

However, I would have coded this function a little differently (should be more efficient)...

Function PullNums(S As String)
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[!0-9]" Then Mid(S, X) = " "
  Next
  If S Like "*[! ]*" Then
    PullNums = Replace(S, " ", "")
  Else
    PullNums = "Null"
  End If
End Function

 

[Nemo74]

Thank you much to the both of you, they all work perfectly! I love learning stuff and you have both shown me some cool things. That 2nd script Rick is very nice and short and I'll be looking to use that and implement that in my future code.

Thanks you guys!