Excel Scientific Notation Without e (7 Quick Tricks)

Download the Practice Workbook

Convert Scientific Notation to Number.xlsx

Method 1 – Apply the INT Function to Convert Scientific Notation to a Number Without e

Our data in the Distance from Earth (m) column is in a scientific format.

Enter the following formula in cell D5 to convert the data of cell C5 to a normal number.

=INT(C5)

In the formula, the INT function converts the format of the number from scientific to normal number.

Use the Fill Handle of Excel to copy the formula in the following cells.

Method 2 – Insert the TRIM Function to Get Rid of Scientific Notation

Enter the following formula:

=TRIM(C5)

Use Fill Handle to copy the formula to the other cells.

Method 3 – Use the TRUNC Function to Avoid Scientific Notation and Get Numbers Without e

Enter the following formula and press Enter.

=TRUNC(C5,0)

Drag the Fill Handle down to the other cells.

0 as the argument for TRUNC, which means we won’t cut any digit after decimal point.

Method 4 – Combination of LEFT, TEXT, FIND, ROUND, and LEN Functions to Remove Scientific Notations

Create a helper column in which the coefficient of the scientific number is separated.
Enter the following formula and press Enter.

=LEFT(TEXT(C5,"0.0E+0"),FIND("E",TEXT(C5,"0.0E+0"))-1)

Drag the Fill Handle for the remaining cells.

Formula Breakdown:

TEXT(C5, “0.0E+0”): This part converts the value in cell C5 to a text format using the “0E+0” format. The “0E+0” format ensures that the number is displayed in scientific notation with one digit before the decimal point and one digit after the decimal point.
FIND(“E”, TEXT(C5, “0.0E+0”)): We used the FIND function to locate the position of the letter “E” within the text generated in the previous step. This helps identify the position where the exponent starts.
FIND(“E”, TEXT(C5, “0.0E+0”))-1: By subtracting 1 from the result of the FIND function, we obtain the position of the last digit of the coefficient in the scientific notation.
LEFT(TEXT(C5, “0.0E+0”), FIND(“E”, TEXT(C5, “0.0E+0”))-1): We used the LEFT function then to extract the leftmost characters from the text generated in step 1, stopping at the position identified in step 3. This effectively gives us the coefficient without the “E” exponent notation.

Enter the following formula to get rid of scientific notation e.

=ROUND(D5*10^(RIGHT(TEXT(C5,"0.0E+0"),LEN(TEXT(C5,"0.0E+0"))-FIND("E",TEXT(C5,"0.0E+0")))),2)

Formula Breakdown:

TEXT(C5, “0.0E+0”): This part converts the value in cell C5 to a text format using the “0E+0” format, ensuring that it is displayed in scientific notation.
FIND(“E”, TEXT(C5, “0.0E+0”)): The FIND function locates the position of the letter “E” within the text generated in step 1, identifying where the exponent starts.
LEN(TEXT(C5, “0.0E+0”)): The LEN function determines the length of the text generated in step 1, giving us the total number of characters.
LEN(TEXT(C5, “0.0E+0”))-FIND(“E”, TEXT(C5, “0.0E+0”)): By subtracting the position of “E” (from step 2) from the total number of characters (from step 3), we obtain the length of the exponent.
RIGHT(TEXT(C5, “0.0E+0”), LEN(TEXT(C5, “0.0E+0”))-FIND(“E”, TEXT(C5, “0.0E+0”))): We used the RIGHT function to extract the rightmost characters from the text generated in step 1, starting from the position we determined in step 4. This gives us the exponent.
D5*10^(RIGHT(TEXT(C5, “0.0E+0”), LEN(TEXT(C5, “0.0E+0”))-FIND(“E”, TEXT(C5, “0.0E+0”)))): This part multiplies the value in cell D5 with 10 raised to the power of the exponent obtained in step 5, effectively calculating the value without the scientific notation.
ROUND(D5*10^(RIGHT(TEXT(C5, “0.0E+0”), LEN(TEXT(C5, “0.0E+0”))-FIND(“E”, TEXT(C5, “0.0E+0”)))), 2): We used the ROUND function to round the result obtained in step 6 to two decimal places.

Method 5 – Use the Copy Paste Options to Convert Scientific Notation to Numbers Without e

Select the cells with data and copy by pressing Ctrl + C.

Right-click on the cell where you want the result to appear.
Select Paste as Values from the Paste Special option of the context menu.

The numbers don’t have the scientific notation anymore.

Method 6 – Apply the Custom Number Format to Convert Scientific Notation Without e

Select the cells with data.
Go to Number Format box of the ribbon and select More Number Formats.

Select Number from the number format tab and choose your required decimal values.
Press OK.

You will get the number without e.

Method 7 – Insert an Apostrophe Before Number to Remove e

Insert an apostrophe “‘” sign before the number and press Enter.

The green sign at the edge of the cells declare that you have stored the numbers as text now.

Why Does Excel Change Long Numbers to e+?

When a number has more than a particular number of digits, Excel automatically converts it to the “e+” scientific notation format. This behavior is Excel’s default setting and is designed to aid in organizing and presenting large numbers without consuming an excessive amount of cell space.

Excel can more effectively represent very big or very small numbers by employing scientific notation with the “e+” format. A number is represented using the “e+” format as a coefficient multiplied by 10 and raised to a specific power (exponent). For instance, 1.23E+10 denotes 1.23 raised to the power of 10, or 1.23 multiplied by 10.

Excel’s default behavior can be changed by altering the cell’s formatting options or by extracting and manipulating data with formulas.

<< Go Back to Scientific Notation in Excel | Number Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!