Written by Allen Wyatt (last updated January 25, 2025)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365
Donna handles building management for her company, and she routinely has to enter room locations into spreadsheets. For instance, 2W07 is second floor, west wing, room 7. When Donna enters a room in the east wing, such as 4E12, Excel converts her entry to a number using exponentiation. She wonders if there is a way to stop this type of parsing as she's entering information.
The short answer is yes, there is a way. In fact, there are three ways you can approach this issue. All methods involve forcing Excel to view your entry as text rather than as a number.
First, you can signify that you are entering a text value by prefacing your entry with an apostrophe. For instance, instead of entering 4E12, you would enter '4E12. The leading apostrophe would not show up in the cell; it is only used to indicate that you are entering text.
Second, you could format the cells in which you are entering room numbers as text. This should be done before you start your data entry. Just select the cells (or, entire rows or columns) and, on the Home tab of the ribbon, use the Number Format drop-down list to choose Text. No, anything you enter into those cells will be treated as text by Excel, without the need to preface it with an apostrophe.
Third, if you are using Excel 2024 or Excel in Microsoft 365, you can follow these steps:
Figure 1. The Data options in the Excel Options dialog box.
Now, when you enter information in your workbook, Excel won't do any exponent parsing. All your entries that could otherwise be parsed as exponents will, instead, be treated as text.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7789) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.
Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!
Wouldn't it be great if you could have Excel display some text in a cell only when that cell is empty? Unfortuantely, ...
Discover MoreWhen you are entering information in a worksheet, it sure would be handy to have a way to "mask" the information being ...
Discover MoreUsing the mouse to select a large cell range can be frustratingly slow. If you want to make copying to a large range of ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2025-01-27 09:21:17
Erik
If you use the third option - changing Excel settings - will opening the file on another computer without that setting change result in errors?
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments