Replace a 00 dialing prefix with a plus

Turn 0012015550123 into +12015550123, and flag numbers that don't start with 00.

What you'll make

In Excel: 0012015550123 becomes +12015550123.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1LEFT(A8,2)="00"Checks that the number starts with 00.Yes
2"+"&MID(A8,3,99)Puts + in place of the 00."+12015550123"
3"Review dialing prefix"What the cell says for any other number.Not used for row 8
4IF( … )Uses part 2 when part 1 says yes, otherwise part 3."+12015550123"

The result is text, so no digits are lost. The numbers here are made-up.

=IF(LEFT(A8,2)="00","+"&MID(A8,3,99),"Review dialing prefix")

How to use it

Yellow bar at the top? Click Enable Editing.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. Use your own numbers. Paste phone numbers that start with 00 into the green column of the Template tab. The + numbers fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Use this only on a list you know writes 00 before the country code. Paste the numbers as text so the 00 stays.

Microsoft's help: LEFT function