Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Thanked 0 Times in 0 Posts

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

    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.



  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

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

    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts