Search code examples
excelvbaletters

Remove letters from a cell leaving numbers only

I am trying to remove all letters from a cell and leave the numbers remaining. I have found bits of code and other questions on here but none are making much sense to me.

I have in cell E23 "as12df34" and want the value of Cell E23 to read "12 34"

Can anyone help with this query please?

Solution

  • You could use a regular expression:

    Sub UsageExample()
  Dim cl

  ' iterate each cell
  For Each cl in Range("Sheet1!A1:A100")
    ' replace each non digit sequence by a space
    cl.Value = ReplaceRe(cl.Value, "\D+", " ")
  Next

End Sub

Public Function ReplaceRe(text As String, pattern As String, replacement) As String
  Static re As Object
  If re Is Nothing Then
    Set re = CreateObject("VBScript.RegExp")
    re.Global = True
  End If
  re.pattern = pattern
  ReplaceRe = re.Replace(text, replacement)
End Function