# How to remove a leading apostrophe from cells in Excel

When the apostrophe is a real character in the text, drop it with =IF(LEFT(A2)="'",MID(A2,2,LEN(A2)),A2).

**Excel:** `=IF(LEFT(A2)="'",MID(A2,2,LEN(A2)),A2)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| ID (imported) | Clean |
| '00123 |  |
| '04471 |  |
| 10442 |  |
| 'A-2290 |  |
| '0099 |  |

Formula in B2, filled to B6: `=IF(LEFT(A2)="'",MID(A2,2,LEN(A2)),A2)`

Result: 00123; 04471; 10442; A-2290; 0099

## How it works

Some CSV and database exports put a real apostrophe in front of IDs to protect their leading zeros. LEFT checks the first character, and MID from position 2 returns everything after it. IDs without an apostrophe pass through unchanged, and the result stays text, so "00123" keeps its zeros.

## Other ways

- `=SUBSTITUTE(A2,"'","",1)`: Removes the first apostrophe. Shorter, but it would also remove one in the middle, as in "O'Neil".

## Common mistakes

- An apostrophe you typed yourself in Excel is a hidden prefix, not part of the value: LEFT(A2) doesn't see it. Those cells are already text; convert them with VALUE, or use Data > Text to Columns > Finish.
- Converting to numbers drops leading zeros ("00123" becomes 123). Keep IDs as text.

## Questions

**How do I turn the cleaned IDs into numbers?**

Wrap the formula in VALUE: `=VALUE(IF(LEFT(A2)="'",MID(A2,2,LEN(A2)),A2))`.

**Does Google Sheets use the same hidden apostrophe?**

Yes. Typing '00123 stores the text 00123, and the formula above handles apostrophes that are part of the text.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/remove-leading-apostrophe