Read national digits after a calling code

Turn +39065550123 with its given code 39 into 065550123, keeping the zero.

What you'll make

In Excel: +39065550123, code 39 becomes 065550123.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1LEFT(A8,LEN(B8)+1)="+"&B8Checks the phone starts with + and this code.Yes
2MID(A8,LEN(B8)+2,99)Keeps everything after the + and the code."065550123"
3"Review supplied calling code"What the cell says when the start doesn't match.Not used for row 8
4IF( … )Uses part 2 when part 1 says yes, otherwise part 3."065550123"

The result is text, so a leading zero stays. The numbers here are made-up.

=IF(LEFT(A8,LEN(B8)+1)="+"&B8,MID(A8,LEN(B8)+2,99),"Review supplied calling code")

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 phones that start with + into the green Phone column and each one's calling code next to it. The national numbers fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Give each row the calling code your source says it has. The formula won't guess the country or drop any zero.

Microsoft's help: LEFT function