'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix Excel Adding Apostrophes, Quotation Marks, or Arrows Before Data
Question details
The user needs to remove automatic apostrophes, quotation marks, or left-facing arrows that continuously appear before cell values in Excel.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Entering numerical data such as phone numbers where Excel automatically inserts hidden formatting characters.
- Observed behavior
- Unwanted symbols like apostrophes and arrows appear in the cells and the formula bar, and they return even after being manually deleted.
Before troubleshooting, verify if the affected cells contain formulas or plain text, and highlight a small range of sample cells to test these formatting fixes before applying them to your entire spreadsheet.
Disable Transition Navigation Keys to Remove Arrows
This resolves the issue of left-facing arrows appearing before your cell data, which is commonly caused by legacy Lotus 1-2-3 compatibility settings being enabled.
Excel retains older compatibility settings to support files originally created in legacy spreadsheet software. Disabling this specific setting will stop the arrows from appearing without affecting your actual data.
Click on the 'File' tab in the top-left corner of the ribbon and select 'Options' at the bottom of the menu.
In the Excel Options dialog box, click on the 'Advanced' tab on the left-hand sidebar.
Scroll down to the very bottom to find the 'Lotus compatibility' section. Uncheck the box for 'Transition navigation keys'.
Click 'OK' to save the settings. Check your worksheet to confirm the left-facing arrows have been removed.
Clear Hidden Apostrophes by Changing Cell Formatting
Excel uses a hidden leading apostrophe to force numbers to be stored as text. Changing the cell format and refreshing the data removes this requirement.
Format Data Effortlessly with WPS Spreadsheet
WPS Spreadsheet provides a clean, modern interface without confusing legacy settings, allowing you to format phone numbers and text effortlessly while remaining fully compatible with Microsoft Excel files.
- 1. Open Your File: Launch WPS Spreadsheet and open the affected workbook.
- 2. Select Problematic Data: Highlight the cells containing the unwanted apostrophes or formatting issues.
- 3. Open Format Cells: Right-click the selected area and choose 'Format Cells', or press the Ctrl+1 shortcut.
- 4. Apply Correct Formatting: Select the 'Number' or 'Custom' category to properly define how your data should be displayed.
- 5. Confirm and Save: Click 'OK' to apply the clean formatting, instantly removing the forced text characters.
Frequently Asked Questions
Why does Excel add an apostrophe before my numbers?
Excel uses a leading apostrophe as a hidden marker to treat numerical data, such as phone numbers or IDs, as text. This is designed to prevent Excel from dropping leading zeros or automatically converting large numbers into scientific notation.
What are Transition Navigation Keys?
Transition Navigation Keys are a legacy compatibility feature originally designed for users migrating from Lotus 1-2-3 to Excel. When enabled, this setting alters how keystrokes and cell data are interpreted, which can cause left-facing arrows and other unexpected characters to appear before your text.
How can I format phone numbers without using the apostrophe?
Instead of using an apostrophe to force text, select your cells, press Ctrl+1 to open the Format Cells dialog, choose 'Custom', and enter a format code like '0000000000'. This allows Excel to display leading zeros naturally while treating the value as a number.
Will 'Text to Columns' permanently fix the apostrophe issue?
Yes, 'Text to Columns' is one of the fastest ways to bulk-remove stubborn hidden apostrophes. By selecting the column, clicking 'Text to Columns' on the Data tab, and immediately clicking 'Finish', you force Excel to refresh the cell formats and clear the hidden text markers.
