Auto format phone number

Loann McGee

Good morning,

I'd like to have our phone column (text) formatted so that when I input just the numbers (ex 7195555555) it would format the number as (719)555-5555.

 

Help?

Blessings,

Loann

Greg Gates

I don't think you could auto-format it in that same column, but if you create an additional column you can write a formula to do the formatting for you! If my phone number is stored as "8888567895" in a column called Primary Column, the formula might look like this:

 

="(" + LEFT([Primary Column]1, 3) + ") " + MID([Primary Column]1, 4, 3) + "-" + RIGHT([Primary Column]1, 4)

 

Paste that formula in a second column and it should format your phone number for you!

Etienne Desbiens

To make sure you don't get double parenthesis and dashes, I suggest you to use the substitute function before to remove every "(", ")" and "-" before adding them back.

Loann McGee

Thanks, Greg!

 

Paulacosentino

awesome!  Just used this.  Thank you!

 

Colin Entrekin

How does one substitute multiple items in a string?  



(555) 555-5555

For example, if I want to substitute the parentheses, space, and dash in the number above, and convert it into...

5555555555

How would I do that?