Defeating Exponent Parsing

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


1

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:

  1. Display the Excel Options dialog box. (Display the File tab of the ribbon and then click Options.)
  2. At the left of the dialog box click Data. (See Figure 1.)
  3. Figure 1. The Data options in the Excel Options dialog box.

  4. Clear the "Convert Digits Surrounding the Letter 'E' to a Number in Scientific Notation" check box. (This option is near the bottom of the dialog box.)
  5. Click OK.

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Making Wider Footer Margins

Want the margins used in your footers (or headers) to be wider than the margins used in the rest of your document? There ...

Discover More

Storing Sorting Criteria

Need to do the same sorting operation over and over again? Excel doesn't provide a way to save your sorting criteria, but ...

Discover More

Cleaning Up Text in a Macro

Need to remove extraneous characters from a text string? VBA makes it easy through the CleanString method, described in ...

Discover More

Program Successfully in Excel! This guide will provide you with all the information you need to automate any task in Excel and save time and effort. Learn how to extend Excel's functionality with VBA to create solutions not possible with the standard features. Includes latest information for Excel 2024 and Microsoft 365. Check out Mastering Excel VBA Programming today!

More ExcelTips (ribbon)

Combining Columns

Need to concatenate the contents in a number of columns so that it appears in a single column? Excel has no intrinsic way ...

Discover More

Understanding Auto-Population of Cells

Auto-population of your formulas can be a useful tool when you are adding data to your worksheets. It would be even more ...

Discover More

Pasting Leading Zeroes

Paste information into a worksheet, and you may be surprised to see leading zeroes disappear before your eyes. Here's how ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is seven less than 7?

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?


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.