Remove a leading 1 from 11-digit phones

Turn 12015550123 into 2015550123, and flag any other length or start.

What you'll make

In Excel: 12015550123 becomes 2015550123.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1AND(LEN(A8)=11,LEFT(A8,1)="1")Checks for 11 digits that start with 1.Yes
2RIGHT(A8,10)Keeps the last ten digits."2015550123"
3"Review 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."2015550123"

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

=IF(AND(LEN(A8)=11,LEFT(A8,1)="1"),RIGHT(A8,10),"Review 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 with a leading 1 into the green column of the Template tab. The ten digits fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Take out spaces and dashes first. Use this only on US and Canada numbers saved with a leading 1.

Microsoft's help: RIGHT function