Removing non-numerical characters from an Excel worksheet

A question from Askville:

I need an excel macro that will delete all the letters and special chctrs from a cell, while leaving the numbers in tact

Example:

1-216-564-7894 would become 12165647894
a214d546d7894 would become 2145467894

This sub will remove all characters except numbers from the currently selected cell(s):

Sub RemoveNonNumeric()
    For Each Cell In Selection
        MyText = Cell.Value
        MyNewText = ""
        MyLen = Len(MyText)
        For i = 1 To MyLen
            MyChr = Mid(MyText, i, 1)
            If MyChr Like "[1234567890]" Then
                MyNewText = MyNewText + MyChr
            End If
        Next i
        Cell.Value = MyNewText
    Next Cell
End Sub

* * * * *

This entry was posted in Answers, Technology. Bookmark the permalink.

One Response to Removing non-numerical characters from an Excel worksheet

  1. Alex says:

    Thank you for providing a good function. I just added an additional – to the line of signs, to keep numbers like 90459-0127.

    /Alex

Leave a Reply

Your email address will not be published. Required fields are marked *