Data Import & Export

How to Remove a Leading Apostrophe from Excel Numbers

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to remove leading apostrophes or accent graves from imported CSV numbers so the application treats them as numeric values rather than text.

Product: Spreadsheets
Device & OS: not provided
Scenario: Importing numeric data from a CSV file into a spreadsheet where numbers prefix with special characters.
Observed behavior: Numbers contain a leading apostrophe or accent grave, forcing them to be formatted as text, which prevents correct sorting and numerical calculations.

Before you start

Check if your imported data contains identification codes (like zip codes or employee IDs) with leading zeros; removing the apostrophe will drop those leading zeros, so you may want to preserve the text format for those specific columns.

Solution 1Recommended

Use Find and Replace to Remove the Character

The fastest method to remove a specific leading character across a large dataset.

This approach allows you to quickly locate the exact character causing the issue and erase it from the entire selected range.

Copy the leading character

Select one affected cell, enter edit mode, and copy the leading apostrophe or accent grave.

Format the cells

Select all the affected cells. Right-click and change their format to 'General'. If you want to avoid scientific notation for long numbers, format them as 'Number' with zero decimal places.

Open Find and Replace

Press Ctrl + H on your keyboard to open the Find and Replace dialog box.

Replace the character

Paste the copied character into the 'Find what' field. Leave the 'Replace with' field completely empty, and click 'Replace All'.

Scientific Notation Tip: Formatting as 'Number' before replacing the character guarantees that long digits (like credit card numbers) won't instantly convert into scientific notation (e.g., 1.23E+11).

Solution 2

Convert Text Numbers using Text to Columns

An excellent built-in tool for converting cells formatted as text back into actual numbers without manual replacement.

Clean Data Instantly

Quickly Clean Up Imported CSV Data with WPS Spreadsheet

WPS Office offers seamless CSV import handling and powerful data cleanup tools like Text to Columns and Find and Replace, making it incredibly easy to convert text numbers back to usable numeric data.

1. Open your file: Launch WPS Spreadsheet and open your imported CSV or Excel file.
2. Highlight the data: Select the column containing the numbers with leading apostrophes.
3. Use Text to Columns: Go to the Data tab, click Text to Columns, and immediately click Finish.
4. Verify formatting: Check your data to ensure the numbers are right-aligned, confirming they are now recognized as numeric values.

Easily handle CSV data imports without formatting corruptionFully compatible with Microsoft Excel (.xlsx, .csv) formatsPowerful Text to Columns and data cleaning featuresLightweight, fast, and completely free to use

microsoft office alternative - wps office

Frequently Asked Questions

Why do imported CSV numbers have a leading apostrophe?

Applications and database exports often add a leading apostrophe to preserve exact formatting. This forces the spreadsheet to treat the data as text, preventing leading zeros from being dropped or long numbers from converting into scientific notation.

How can I prevent long numbers from turning into scientific notation when removing the apostrophe?

Before you remove the apostrophe using Find and Replace or Text to Columns, you must format the affected cells as 'Number' with zero decimal places. This ensures the full number displays correctly once it is converted to a numeric value.

Can I use a formula to remove the leading apostrophe?

Yes, you can use the VALUE() function. For example, typing =VALUE(A1) in an adjacent column will take the text-formatted number in cell A1 and convert it into a standard, workable numeric value.

Excel & Data Problems