Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts

    Excel Formatting

    Hello All,
    I have an Excel worksheet that has a column of data with various data entries for phone numbers. Some of the users have entered in the data as (123) 456-7890 while other have entered 123-456-7890 or 123.456.7890 I would like to apply the 000-000-0000 format to all cells but it's not working. I'm assuming I need to remove all the extra special characters such as () or . before I can apply the formatting I want. Is there any easy way to achieve this?

    Thanks!

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You could just do a series of Find/Replace operations to replace "(", ")","-" and spaces with nothing.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. The Following User Says Thank You to rory For This Useful Post:

    MOSTATE (2014-11-05)

  4. #3
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    Yes, I did that and it works I just thought there might be another way. I could create a macro for the Find and Replace piece but just wondered if there was an easier way. Thanks so much for your reply!

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    How about a UDF?

    In Cell B1 =PhoneNum(A1) then copy down

    UDF1.png

    Code:
    Public Function PhoneNum(Num) As String
    For I = 1 To Len(Num)
        Select Case Mid(Num, I, 1)
            Case "(", ")", ".", "-": GoTo Skip
        End Select
        PhoneNum = PhoneNum + Mid(Num, I, 1)
    Skip:
    Next I
    PhoneNum = Format(PhoneNum, "###-###-####")
    End Function

  6. The Following User Says Thank You to Maudibe For This Useful Post:

    MOSTATE (2014-11-07)

  7. #5
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    That is Wonderful!...Thanks so much!!

  8. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Mostate,

    If you want to correct the format of the value within the same cell and not having to use a formula in a different cell, place the following code in the sheet's module:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
        For I = 1 To Len(Target)
            Select Case Mid(Target, I, 1)
                Case "(", ")", ".", "-": GoTo Skip
            End Select
            PhoneNum = PhoneNum + Mid(Target, I, 1)
    Skip:
        Next I
        Target = Format(PhoneNum, "###-###-####")
    End If
    End Sub
    Validates for phone numbers in column A rows 1 to 100
    Last edited by Maudibe; 2014-11-16 at 20:44.

  9. #7
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Try this instead
    Function pn(Num) As String
    Dim i As Integer
    For i = 1 To Len(Num)
    If Asc(Mid(Num, i, 1)) >= Asc("0") _
    And Asc(Mid(Num, i, 1)) <= Asc("9") Then
    pn = pn + Mid(Num, i, 1)
    End If
    Next i
    pn = Format(pn, "000-000-0000")
    End Function

  10. #8
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    To do an entire range using the function

    Sub fixphonerange()
    Dim c As Range
    Dim num As Range
    For Each c In Range("E1:e15")
    c = pn(c)
    Next
    End Sub

Posting Permissions

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