How to remove dashes, periods, etc. (Y2K)

Topic

New Reply

I need to remove dashes, periods or other forms of punctuation from a string of numbers, e.g. 024-798-256.5 = 0247982565. The numbers can vary in length. Also, I’d like to retain any leading zeros.

Thanks!

Scott

Reply | Quote

Replies

Copy the following function into a standard module:

Function StripNumber(strVal As String) As String
Dim i As Integer
Dim c As String
Dim strRet As String
For i = 1 To Len(strVal)
c = Mid(strVal, i, 1)
If Asc© > 47 And Asc© < 58 Then
strRet = strRet & c
End If
Next i
StripNumber = strRet
End Function

With 024-798-256.5 in A1, the formula =StripNumber(A1) will return 0247982565

If you want to change the values themselves, select a range of cells and run the following macro:

Sub CleanNumbers()
Dim oCell As Range
Selection.NumberFormat = "@"
For Each oCell In Selection
oCell.Value = StripNumber(oCell)
Next oCell
End Sub

(It uses the StripNumber function)

Reply | Quote