Create a Macros Function

Question

Hello everyone, I would like help with a VBA macro. This macro only extracts numbers, but I can't update it so that the extraction form includes a special character "-" or "/" as shown in the example below.

"00000-000"

“0000/000"

“000/00”

“000-00”

When used, it automatically excludes the "-" or "/"

Can anyone help me?

Function GetNumber(CellRef As String)

Dim StringLength As Integer

StringLength = Len(CellRef)

For i = 1 To StringLength

If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1)

Next i

GetNumber = Result

End Function

Answer 1

Here is the fastest function I've ever seen for this task.

Andreas.

Function JustNumbers(ByVal What As String) As String
  'Return only numbers from What (by Rick Rothstein)
  Dim i As Long, j As Long, Digit As String
  For i = 1 To Len(What)
    Digit = Mid$(What, i, 1)
    If Digit Like "#" Then
      j = j + 1
      Mid$(What, j, 1) = Digit
    End If
  Next
  JustNumbers = Left$(What, j)
End Function

Answer 2

Thank you very much!

Answer 3

If you want to include "-" and "/" in the result:

Function GetNumber(CellRef As String)
    Dim StringLength As Integer
    Dim i As Long
    Dim c As String
    Dim Result As String
    StringLength = Len(CellRef)
    For i = 1 To StringLength
        c = Mid(CellRef, i, 1)
        If IsNumeric(c) Or c = "-" Or c = "/" Then
            Result = Result & c
        End If
    Next i
    GetNumber = Result
End Function