Formatting Issues

Add a Leading Apostrophe to Excel Cells in Bulk

Olivia MillerOlivia Miller Nov 20, 2026 869 views

Question details

Add a leading apostrophe to many Excel values and preserve leading zeros by treating the values as text.

Add a Leading Apostrophe to Excel Cells in Bulk
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the formula

In an empty column next to your data, enter =CONCATENATE("'",A1), replacing A1 with the first source cell.

2
Fill the column

Use the fill handle at the bottom-right corner of the formula cell to copy the formula down alongside the remaining values.

3
Keep the results

Select and copy the formula results, then choose Edit > Paste Special > Values, or the equivalent Paste Special > Values command in your Excel version.

Add a literal apostrophe with a formula
Work with text in WPS Spreadsheets

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. 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
  2. 2. Enter the formula: In an empty column, enter =CONCATENATE("'",A1), adjusting A1 to reference the first value.
  3. 3. Fill down: Drag the formula cell's fill handle down to apply the formula to the remaining rows.
  4. 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.
Add a literal apostrophe to a whole column with one formula and fill-down.Preserve leading zeros that are already stored as text.Open and edit Microsoft Excel XLS and XLSX files.Use a familiar spreadsheet interface for everyday editing.
microsoft office alternative - wps office

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.