Adding Country Code to Column of Phone Numbers

Question

Level 1

4 points

Adding Country Code to Column of Phone Numbers

I have a spreadsheet of 2000 contacts with U.S. phone numbers in a column. How can I add +1 at the beginning of each phone number in Apple Numbers without doing it manually for each number?

Thank you.

John

MacBook, macOS 10.15

Posted on Jan 14, 2022 7:33 AM

Reply

Answer 1

Top-ranking reply

Barry

Level 9

59,951 points

Jan 14, 2022 3:36 PM in response to kob_260

Hi kob'

For purposes of this discussion, I'm assuming the telephone numbers are listed in column D of your table.

Start by clicking any cell in column D.

Press the option key, then tap the left arrow key to add a new column to the left of the phone numbers.

The new column will become column D, the column containing the phone numbers will become column E and columns to the right of this will move one letter up the alphabet.

Click on cell D2 (first cell below the header row) in the new column).

Press = to open the formula editor, and enter the formula below:

"+1 "&E2

Click the green checkmark to confirm the formula and close the editor.

Cell D2 will now display +1 123 456 7890 (where the original phone number in E2 was 123 456 7890)

If your table was a small as the example, your next stp would be to 'grab' the fill control (small yellow circle) and drag down to fill the formula into the rest of the cells in column D.

With 2000+ rows, though, the steps listed below are more efficient:

With cell D2 still selected, press command-C to copy the cell and its formula to the clipboard.
With cell D2 stil selected, scroll to the last row of the table (or to the last row containing a phone number in column E)
Press and hold the shift key, and click on the last cell of column D to add that cell and all cells between it and D2 to the selection.
Press command-V to paste the formula into all cells from D2 to 'this row'.
With those cells still selected, press command-C to copy the formulas and their results, then
go to the Edit menu and choose Paste Formula Results to strip the formulas and paste only the now edited phone numbers into the selected cells.
Check that the numbers have been correctly edited and that the cells containing the edited number no longer contain the formula (see note below).
On confirming correct editing and non dependence on values in column E, it is safe to select column D (by clicking its column reference tab) then to click the v at the right end of that tab and choose Delete selected column.

NOTE: Checking that values in specific cells are NOT dependent on other cells:

Clock on the cell to be tested (in this case, I'm testing cell D8)

Numbers will highlight any cells that are referenced by a formula in the selected cell.

The small box at the lower left corner of the document window (seen below the table here) will say "Formula" and will show the formula in the selected cell (if there is one).

If you have successfully replaced the formulas in column D with the results calculated fy those formulas, there will be no cells highlighted by the formula, and the box will contain information like this:

With that confirmation, it's safe to delete column E.

Regards,

Barry

Reply