Remove a leading apostrophe from an ID
Turn '000123 from an export into 000123. Leading zeros stay.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | LEFT(A8,1)=CHAR(39) | Checks whether the first character is an apostrophe. | TRUE |
| 2 | MID(A8,2,999) | Keeps everything from the second character on. | "000123" |
| 3 | A8 | Otherwise keeps the ID as it is. | Not used for row 8 |
| 4 | IF( … ) | Takes off one apostrophe, only at the start. | "000123" |
CHAR(39) is the apostrophe. The IDs here are made-up.
=IF(LEFT(A8,1)=CHAR(39),MID(A8,2,999),A8)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 IDs. Paste IDs into the green column of the Template tab. Clean IDs fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
An apostrophe you type yourself to keep zeros isn't stored in the cell. This is for ones that came in with the export.
Microsoft's help: CHAR function
OTHER ROUTES YOU MIGHT LIKE

Return a short stored ID as text
123 as a number → 123 as text
Get the template →
Keep missing-ID markers blank
NULL → blank; 000000001 → 000000001 (zeros kept)
Get the template →
Pad a code with zeros to a set width
A7 + width 4 → 00A7
Get the template →
Merge a few test letters before the whole mailing
Whole mailing → records 2 to 4 only
Take this route →