formatting phone numbers

[SteveS007]

I need help formatting a bunch of phone numbers from this (916) 123-4567 to adding a 119161234567.

[#2]

going from this (916) 123-4567 to adding a 1 in front of the number to this 19161234567.

[#3 · Accepted solution]

@SteveS007 

You can use Excel's built-in functions to format phone numbers as you described. Here's a step-by-step guide on how to add "1" in front of phone numbers like (916) 123-4567:

Assuming your phone numbers are in column A:

1. In an empty column (e.g., column B), starting in the first row where you want to add the formatted numbers (let's say, B1), enter the following formula:

=CONCATENATE("1", SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "(", ""), ")", ""), "-", ""), " ", ""))

This formula removes the parentheses, hyphen, and adds "1" in front of the remaining digits.

1. Press Enter to apply the formula to the cell.
2. Copy the formula down for the rest of the phone numbers in column A by selecting the cell with the formula (B1), dragging the fill handle (a small square at the bottom-right corner of the selected cell) down to cover the desired range.

Excel will apply the formula to each cell in column B, creating the formatted phone numbers with "1" in front of them.

Now, column B will contain the phone numbers in the format you specified (e.g., 19161234567). You can copy and paste these values if you want to remove the formulas and keep only the formatted numbers. The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark them as helpful and like it!

This will help all forum participants.

[#4]

This is super helpful and exactly what I needed. Thank you!

[#5]

yw