Convert Scientific Notation to Numbers in Excel

Add to Favorites
Author: | Edits: don

How to convert scientific numbers to show their full amount; this method also allows you to retain the appearance of text in case you have a mixed list of text and numbers in scientific notation format.

There are a number of ways to do this and what you should use depends on your dataset.

Sections:

Convert Using a Formula

Convert Using Formatting

Input a Number so it's Never Converted to Scientific Format

Notes

Convert Using a Formula

This method shouldn't disturb any text that might be in your list (read the bottom of this section for the exception).

This requires inputting a formula in a column next to the number.

=TRIM(cell_reference)
  1. Go to the column next to your number(s) and type =TRIM(A1)
    379095f0523469304ca0233947fa705d.png
    A1 is the cell that contains the number.
  2. Once you hit enter, it turns into the complete number:
    6edb1b3026b9ff18ed08ce4d858d0eb2.png
  3. Replace the old column of data using copy/paste special. Ctrl + C and then Alt + E + S + V + Enter.

The TRIM() function removes extra spaces from the front and back of a cell in Excel. This will not harm or mess-up any text cells that you might have in your list when you copy the formula down unless those text cells are supposed to have extra spaces around them. If you need to retain extra spaces, you can use UPPER() or LOWER() or PROPER(), though each will change the case of the text.

Convert Using Formatting

This method keeps all of your data in the same place and will not harm any text that might be in your list.

  1. Select the cell with the numbers and right-click on them and click Format Cells...
    4225b5961d50971d9bfc517931b8db59.png
  2. Go to the Number tab and click the Custom category:
    cc062e4a79b1bad958219997a216a6c9.png
  3. Under where it says Type: input a 0
    443011d1c64cd4c20ad43381282dc011.jpg
    You should now see the entire number appear just above where you entered the 0.
  4. Hit OK and the number in the spreadsheet should now be clearly visible.
    cbed2757ea11b45afb6337af8b17d14c.png

This is a great method to use; it's simple, fast, and reliable.

Input a Number so it's Never Converted to Scientific Format

If you are entering numbers by hand, you can use this quick tip to prevent them from converting to scientific format:

Type a single quotation mark/apostrophe in front of the number.

3fad078a1bf9a088c79b0920cdbad71f.png

Notice the comma in front of the number in cell A5.

When I hit Enter, we still see the entire number.

e7d398a6a78a7eef00477926ce8065c8.png

The apostrophe will still be in the cell, but you will only see it when you go to edit the cell.

Notes

Unfortunately, Excel has limitations when it comes to numbers and sometimes Excel thinks it's helping us when, really, it is just being a pain in the neck. This tutorial is meant to show you a few methods for getting your numbers back.

Make sure to download the file attached to this tutorial to work with these examples in Excel.


Excel Function: TRIM()
Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Our Excel Courses

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.

Similar Content on TeachExcel
Convert Numbers Stored as Text to Numbers in Excel
Tutorial: I'll show you 4 ways to convert numbers stored as text to numbers in Excel.  This situat...
Store Large Numbers in Excel
Tutorial: I will show you how to display large, even huge, numbers in Excel.  In Excel, you can't ...
Format Cells as a Scientific Number in Excel Number Formatting
Macro: This free Excel macro formats selected cells in the Scientific number format in Excel. Thi...
Convert Month Name to Number and Vice Versa in Excel
Tutorial: How to convert a month's name, such as January, into a number using a formula in Excel; al...
Keep Leading Zeros in Numbers in Excel - 2 Ways
Tutorial: I'll show you 2 ways to add and keep leading zeros in front of numbers in Excel. These t...
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
Macro: This free Excel macro illustrates all of the possible parameters and arguments that yo...