Function to strip all other than numbers from a field 1

[Moss100]

Hello I have a field InvoiceNumber

The invoice data sometimes has prefix or postfix - ie. eet3546 or ibb876373 or 2355456llk

I would like a function which removes everything and just leaves the numbers with no spaces or other characters.

Could someone help with this? I really can't seem to make progress with this.

Thanks Mark - Keep safe

 

[Andrzejek]

I am sure there is a better way, but ...

Option Explicit

Private Sub Command1_Click()
Dim str1 As String
Dim str2 As String
Dim str3 As String

str1 = "eet3546"
str2 = "ibb876373"
str3 = "2355456llk"

Debug.Print "The value of " & str1 & " is " & [blue]MyValue[/blue](str1)
Debug.Print "The value of " & str2 & " is " & [blue]MyValue[/blue](str2)
Debug.Print "The value of " & str3 & " is " & [blue]MyValue[/blue](str3)

End Sub
[blue]
Private Function MyValue(ByRef str As String) As Long
Dim x As Integer
Dim strOut As String

For x = 1 To Len(str)
    If IsNumeric(Mid(str, x, 1)) Then
        strOut = strOut & Mid(str, x, 1)
    End If
Next x

MyValue = CLng(strOut)

End Function[/blue]

You get:[tt]
The value of eet3546 is 3546
The value of ibb876373 is 876373
The value of 2355456llk is 2355456
[/tt]


---- Andy

There is a great need for a sarcasm font.

 

[Moss100]

Thats great - thank you very much

Regards Mark

 

[Moss100]

I have tested it - and it works well.

I notice it is fine if there is a 10 or less digit number, but it crashes if 11 digits or more.

Is there a way to fix that?

Thank you Mark

 

[Andrzejek]

That is because of the limit of Long.

You could modify the Function to:

Private Function MyValue(ByRef str As String) [red]As Double[/red]
Dim x As Integer
Dim strOut As String

For x = 1 To Len(str)
    If IsNumeric(Mid(str, x, 1)) Then
        strOut = strOut & Mid(str, x, 1)
    End If
Next x

MyValue = [red]CDbl[/red](strOut)

End Function

But you may get back the huge number in scientific notation

You can simply do:

Private Function MyValue(ByRef str As String) [red]As String[/red]
Dim x As Integer
Dim strOut As String

For x = 1 To Len(str)
    If IsNumeric(Mid(str, x, 1)) Then
        strOut = strOut & Mid(str, x, 1)
    End If
Next x

MyValue = strOut

End Function

and you get the String with just the digits (numbers)...


---- Andy

There is a great need for a sarcasm font.

 

[Moss100]

Fantastic - thank you