Phone Number Formatting Formula?

Freddy Bustillos

Is there a formula that will convert manually entered phone numbers to conform to the following format? The phone numbers are currently being entered in every different format imaginable.

Desired format...  (xxx) xxx-xxxx

Paul Newcome

EDIT: Upon further testing I found an issue with my originally provided solution. "-" will not throw an error within a VALUE function, so it will get pulled when we move to the formula in the Join column. I have updated the Join column formula to account for this.

They COULD be, but it would increase the complexity. You can always hide the columns used to parse. I use a very similar setup for a sheet where I need to replace alphabetical characters with number.

First you would create as many columns as you think you will need at the maximum for parsing. 20 should be more than enough (your desired format has 14 digits already). Then in row 1 you would put the numbers 1 through 20. I generally name these columns the text version of the number just to help keep things organized.

One.....Two.....Three.....Four.....Five...........................................

..1...........2............3...........4...........5..................................................

The formula to parse that would go in the One column is

=MID($[Phone Number]@row, One$1, 1)

You can then dragfill this out across the remaining parsing columns to parse out the entered data.

Next we will add another column that I will simply call Join. This column will pull the numbers only from the parsed cells using:

=JOIN(COLLECT(One@row:Twenty@row, One@row:Twenty@row, NOT(ISERROR(VALUE(@cell)))))

=SUBSTITUTE(JOIN(COLLECT(One@row:Twenty@row, One@row:Twenty@row, NOT(ISERROR(VALUE(@cell))))), "-", "")

Now that we have a string of only numbers, there are numerous options for the placement of what you need for your desired formatting.

Area Code:

=LEFT(Join@row, 3)

Next 3:

=MID(Join@row, 4, 3)

Last 4:

=RIGHT(Join@row, 4)

Using those 3 functions, you can string them together with your formatting in place like so:

="(" + LEFT(Join@row, 3) + ") " + MID(Join@row, 4, 3) + "-" + RIGHT(Join@row, 4)

So in all my solution requires 22 columns. 21 can be hidden (One through Twenty and Join) and the last column is up to you whether you want to display it or not as it will contain that last formula that pieces everything back together in your desired format.

Paul Newcome

There isn't a single direct formula that will do it, but you can parse out the field so that each character is in it's own cell then pick and choose what gets joined back together and how.

Freddy Bustillos

Good morning Paul, do you have an example of what that formula might look like? Also, could the individual cells in which the phone number would be parsed be on a separate sheet, so as not to be distracting?

Paul Newcome

EDIT: Upon further testing I found an issue with my originally provided solution. "-" will not throw an error within a VALUE function, so it will get pulled when we move to the formula in the Join column. I have updated the Join column formula to account for this.

They COULD be, but it would increase the complexity. You can always hide the columns used to parse. I use a very similar setup for a sheet where I need to replace alphabetical characters with number.

First you would create as many columns as you think you will need at the maximum for parsing. 20 should be more than enough (your desired format has 14 digits already). Then in row 1 you would put the numbers 1 through 20. I generally name these columns the text version of the number just to help keep things organized.

One.....Two.....Three.....Four.....Five...........................................

..1...........2............3...........4...........5..................................................

The formula to parse that would go in the One column is

=MID($[Phone Number]@row, One$1, 1)

You can then dragfill this out across the remaining parsing columns to parse out the entered data.

Next we will add another column that I will simply call Join. This column will pull the numbers only from the parsed cells using:

=JOIN(COLLECT(One@row:Twenty@row, One@row:Twenty@row, NOT(ISERROR(VALUE(@cell)))))

=SUBSTITUTE(JOIN(COLLECT(One@row:Twenty@row, One@row:Twenty@row, NOT(ISERROR(VALUE(@cell))))), "-", "")

Now that we have a string of only numbers, there are numerous options for the placement of what you need for your desired formatting.

Area Code:

=LEFT(Join@row, 3)

Next 3:

=MID(Join@row, 4, 3)

Last 4:

=RIGHT(Join@row, 4)

Using those 3 functions, you can string them together with your formatting in place like so:

="(" + LEFT(Join@row, 3) + ") " + MID(Join@row, 4, 3) + "-" + RIGHT(Join@row, 4)

So in all my solution requires 22 columns. 21 can be hidden (One through Twenty and Join) and the last column is up to you whether you want to display it or not as it will contain that last formula that pieces everything back together in your desired format.

Freddy Bustillos

Thank you Paul! That did the trick!

Paul Newcome

Happy to help! 👍️