Auto format phone number

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

Find more posts tagged with

Non-profit

Formulas

Project Management

Media & Entertainment

Comments

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?