how to create macro using replace function? [closed]

This function returns a string containing all numeric characters in the cell it is given as a parameter. I think that is what you want?

Function remove_non_numbers(r As Range) As String
  Dim oRegEx As Object

  Set oRegEx = CreateObject("vbscript.regexp")
  oRegEx.Pattern = "[^\d]+"
  oRegEx.Global = True

  remove_numbers = oRegEx.Replace(r, vbNullString)
End Function