Add a Leading Apostrophe to Excel Cells in Bulk
Question details
Add a leading apostrophe to many Excel values and preserve leading zeros by treating the values as text.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs to process thousands of cells, potentially in an older version such as Excel 2000.
- Observed behavior
- The desired result is text values that retain their leading zeros, with an apostrophe added where needed.
Check whether the leading zeros are stored in the values or only displayed by a custom number format. If zeros have already been lost, you will need the original text or a known fixed width to restore them.
Add a literal apostrophe with a formula
Use a helper column to add an apostrophe to thousands of values, including in Excel 2000.
This formula creates a text string containing an apostrophe. That literal character is not the same as Excel's hidden apostrophe text-prefix marker and may appear in the cell.
The formula preserves zeros already stored as text, but it does not reproduce zeros shown only by number formatting. If every value should have five digits, use =CONCATENATE("'",TEXT(A1,"00000")) instead.
In an empty column next to your data, enter =CONCATENATE("'",A1), replacing A1 with the first source cell.
Use the fill handle at the bottom-right corner of the formula cell to copy the formula down alongside the remaining values.
Select and copy the formula results, then choose Edit > Paste Special > Values, or the equivalent Paste Special > Values command in your Excel version.

Use a VBA macro to apply Excel's text prefix
Use this method when you want to convert the displayed contents of selected cells to text using Excel's leading apostrophe prefix.
Import identifiers as text to preserve leading zeros
Use a text import when the original data is in a text or CSV file and the zeros must be preserved from the start.
Add apostrophes to values in WPS Spreadsheets
Use a helper-column formula in WPS Spreadsheets to add a literal apostrophe to values in bulk, then paste the results as values.
- 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
- 2. Enter the formula: In an empty column, enter =CONCATENATE("'",A1), adjusting A1 to reference the first value.
- 3. Fill down: Drag the formula cell's fill handle down to apply the formula to the remaining rows.
- 4. Paste values: Copy the results and use Paste Special > Values to keep the text without the formulas. Check whether the literal apostrophe appears as intended.

Frequently Asked Questions
Will adding an apostrophe restore leading zeros that Excel already removed?
No. Use the original text data if available. If every value has a known width, a formula such as =TEXT(A1,"00000") can restore a five-digit representation.
Why can I see the apostrophe produced by the formula?
The formula creates a literal apostrophe as part of the text. Excel's manually entered leading apostrophe is a text-prefix marker that is normally hidden in the cell.
Does formatting existing cells as Text convert their values?
Changing Format Cells > Number > Text affects the format but does not reliably convert existing numeric values or restore lost zeros. Use an import, formula, or conversion method instead.
Can the VBA macro change formulas into text?
Yes. The macro replaces each selected cell's contents with its displayed text, so formulas in the selection will no longer be formulas.




