Phone Number formatting

Options

Is there a way to format phone numbers in SmartSheet. For example - I have about 400 phone numbers and the format is in different formats - 555-555-5555 or (555) 555-5555.


Is there a formula or an option within SmartSheet that I am missing?

Best Answers

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭
    edited 01/12/21 Answer ✓
    Options

    You can do that with a giant combinations of SUBSTITUTE( to get replace with blanks any/all characters that aren't numbers (like =SUBSTITUTE([Old Phone #]@row, "-", "") ) to make a helper column. Then with a combination of LEFT(, MID(, and RIGHT( to add in the formatting of your liking (like =LEFT([New Phone #]@row, 3) + "-" ...etc, where [New Phone #] would be your column with all the odd characters subbed out).

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

  • Carol-Anne Cerbone
    Carol-Anne Cerbone ✭✭✭✭✭
    Answer ✓
    Options

    Thanks Jason! Appreciate the information

Answers

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭
    edited 01/12/21 Answer ✓
    Options

    You can do that with a giant combinations of SUBSTITUTE( to get replace with blanks any/all characters that aren't numbers (like =SUBSTITUTE([Old Phone #]@row, "-", "") ) to make a helper column. Then with a combination of LEFT(, MID(, and RIGHT( to add in the formatting of your liking (like =LEFT([New Phone #]@row, 3) + "-" ...etc, where [New Phone #] would be your column with all the odd characters subbed out).

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

  • Carol-Anne Cerbone
    Carol-Anne Cerbone ✭✭✭✭✭
    Answer ✓
    Options

    Thanks Jason! Appreciate the information