How do I create a phone number format with only dashes and numbers

Question

I am trying to create a phone number format with only numbers and dashes.  I have a file with phone numbers formatted as (###) ###-####.  I want to reformat to ###-###-####.  I tried and the attached screen shot does not work.

Answer 1

This is so simple to do. No macros are needed.

In the column next to the column you want to format, enter the phone number in the desired format and press return. Then press Control-e to invoke Flash Fill. The column will fill with the proper formatting.

Answer 2

Bernie, See column J Phone field--here is the link:

Member file

Answer 3

Your problem is exactly what I described - your entries have (  ) - and spaces so they are strings, not numbers.

Run this macro with that sheet active to fix everything:

Sub FixPhoneNumbers()

    With Range("J:K")

    .NumberFormat = "###-###-####"

    .Replace What:=")", Replacement:="", LookAt:=xlPart

    .Replace What:="(", Replacement:="", LookAt:=xlPart

    .Replace What:="-", Replacement:="", LookAt:=xlPart

    .Replace What:=" ", Replacement:="", LookAt:=xlPart

    .HorizontalAlignment = xlCenter

    End With

End Sub

Answer 4

Wow, this is above my pay grade.  I have the document open, where do I run the macro, in the sheet, in the active tab, or external.  The column headings are different in the second tab.  The first tab is the one I am most concerned with.

Answer 5

Follow the instructions here:

https://peltiertech.com/how-to-use-someone-elses-macro/

To use the macro on another sheet (or tab as you call them), simply select a cell or cells with a phone number (or numbers) - in your example sheet, you could select cells J2 and K2 -  and run this version of the macro:

Sub FixPhoneNumberss()

    With Selection.EntireColumn

    .NumberFormat = "###-###-####"

    .Replace What:=")", Replacement:="", LookAt:=xlPart

    .Replace What:="(", Replacement:="", LookAt:=xlPart

    .Replace What:="-", Replacement:="", LookAt:=xlPart

    .Replace What:=" ", Replacement:="", LookAt:=xlPart

    .HorizontalAlignment = xlCenter

    End With

End Sub