Remove a leading 1 from 11-digit phones
Turn 12015550123 into 2015550123, and flag any other length or start.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | AND(LEN(A8)=11,LEFT(A8,1)="1") | Checks for 11 digits that start with 1. | Yes |
| 2 | RIGHT(A8,10) | Keeps the last ten digits. | "2015550123" |
| 3 | "Review prefix" | What the cell says for any other number. | Not used for row 8 |
| 4 | IF( … ) | 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.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- 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.
- 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
OTHER ROUTES YOU MIGHT LIKE

Get a phone number from a tel link
tel:+12015550123;ext=12 → +12015550123
Get the template →
Read the extension from a tel link
tel:+12015550123;ext=0012 → 0012
Get the template →
Replace a 00 dialing prefix with a plus
0012015550123 → +12015550123
Get the template →
Hide "Phone:" when there's no phone number
(blank) → no "Phone:" line
Take this route →