6 Ways to Remove Scientific Notation in Excel

By default, Microsoft Excel automatically stores big and small integers in Scientific Notation. Excel has a maximum number length of 11 digits. Excel automatically transforms numbers with more than 11 digits to scientific notation to accommodate this restriction. In this article, we are going to show you 6 easy ways to remove scientific notation in excel.


📁 Download Excel File


Learn to Remove Scientific Notation in Excel with These 6 Suitable Approaches

When you work in excel sometimes you may encounter a value like 1.54E+18. As the value is too large it stored the value automatically in scientific notation. Here we will illustrate the ways to remove these scientific notations using the following dataset.

Dataset of how to remove scientific notation in excel


Approach

1.  Using Format Cells Option

In this method we will use the cell formatting option to remove scientific notation following the steps given below.

⬇️⬇️ STEPS ⬇️⬇️

  • First we will go to the Cell Formatting worksheet.

how to remove scientific notation in excel using cell formatting

  • Then we will copy the values from B6 to B9 and paste them in the without scientific notation column.

remove scientific notation applying format cells option

  • Further select cell C6.

how to remove scientific notation in excel applying cell formatting

  • Then right click on the mouse and a dialog box will open . From the dialog box select the Format Cells option.

  • After clicking Format Cells you will get a window like below. In this window select the Number first then change the Decimal places to 0 and click OK.

how to remove scientific notation in excel utilizing cell formatting

  • After clicking OK we will see that the scientific notation has changed.

  • However, remember that for decimals with a significant number of 0’s before a non-zero digit, you must raise the decimal places; otherwise, the values will display 0. So for the value 5.32E-12 we will increase the decimal places to a suitable number and click ok. Then we will get our desired value.

  • Now we will follow the previous steps for the other two values and we will obtain our desired result.


Approach

2. Using TRIM Function

In this method we will use excel TRIM function. Trim function is normally used to remove extra spaces from a text however it can also remove scientific notation.

⬇️⬇️ STEPS ⬇️⬇️

  • First go to the TRIM worksheet.
  • Select cell C6.

applying TRIM function

  • Enter the following formula.
=TRIM(B6)

remove scientific notation in excel utilizing TRIM function

  • Click the Enter key.

 utilizing TRIM function

  • Then select cell C6 and drag the Fill handle icon from C6 to C9.

how to remove scientific notation in excel using TRIM function


Approach

3. Utilizing CONCATENATE Function

By using the CONCATENATE function, we can also simply eliminate scientific notation. This function functions identically to the TRIM function and produces the same result. Although the CONCATENATE function is often used to merge two or more texts in a single cell, it may also be used to eliminate scientific notation.

⬇️⬇️ STEPS ⬇️⬇️

  • First go to the CONCATENATE worksheet.
  • Select cell C6.

 utilizing CONCATENATE function

  • Enter the following formula.
=CONCATENATE(B6)

how to remove scientific notation in excel using CONCATENATE function

  • Then click the Enter key.

remove scientific notation in excel applying CONCATENATE Function

  • Then select cell C6 and drag the Fill handle icon from C6 to C9.


Approach

4. Using UPPER Function

The UPPER function may also be used to eliminate scientific notation. The UPPER function essentially converts the whole text to all capital letters. However, if you use the function to remove the scientific notation, you will get the same result as with the preceding techniques.

⬇️⬇️ STEPS ⬇️⬇️

  • First go to the UPPER worksheet.
  • Select cell C6.

utilizing UPPER function

  • Enter the following formula.
=UPPER(B6)

  • Click the Enter key.

how to remove scientific notation in excel uSING UPPER function

  • Then select cell C6 and drag the Fill handle icon from C6 to C9.


Approach

5. Using Text to Columns Feature

In the first approach we use the Text to Column option from the toolbar and remove scientific notation in excel by following the steps given bellow.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, go to the Text to Column worksheet and copy the values from with scientific notation and paste them in without the scientific notation column.

 utilizing Text to Column Feature

  • Then from the excel toolbar go to Data and then click Text to Columns.

  • After clicking Text to Column you will Convert text to column wizard. Select Fixed width there and click Next.

remove scientific notation in excel applying Text to Column Feature

  • Then you get another window like below and click Next.

remove scientific notation implementing Text to Column Feature

  • Then select Text in the last step and click on Finish.

 using Text to Column Feature

  • And we can see here we have removed the scientific notation. Then apply the same process for other cells and finally we will be able to remove scientific notation from all the cells.

Here finally we can see that we have removed the scientific notation from every number by applying Text to Column option.


Approach

6. Adding Apostrophe

By adding apostrophe at the beginning of the number we can also remove the scientific notation. Here we will show you how to perform this operation.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, go to the Apostrophe worksheet.

  • Then copy and paste the values from column B to Column C.

utilizing Apostrophe

  • Select cell C6. Double click on the cell and put an apostrophe at the beginning of the number and then click enter.

  • You will see that the scientific notations have been removed.

  • Repeat the same procedure for all the values and you will see that the scientific notations have been removed.


How to Convert Scientific Notation to Text in Excel

Sometimes it may be necessary to convert the Scientific Notation to Text format to display the number in its entirety. If a number comprises more than 11 digits, one of the basic capabilities of Microsoft Excel converts it to Scientific Notation. In this post, we will examine the easy way available for changing the format to our liking. Here we will use the TEXT function.

⬇️⬇️ STEPS ⬇️⬇️

  • First go to the TEXT worksheet.
  • Select cell C6.

 utilizing TEXT function

  • Enter the following formula.
=TEXT(B6,”0″)

  • Click the Enter key.

 using TEXT function

  • Then select cell C6 and drag the Fill handle icon from C6 to C9.


📝 Takeaways from This Article

📌  In this article we learned six easy methods to remove notation in excel.

📌  We also learned few excel functions and their use. For example, TRIM, CONCATENATE, UPPER and TEXT.

📌  We also used few options in excel and learned about them.


Conclusion

In this post, we tried to show every method to remove scientific notation in excel. So that you may choose the appropriate approach for you. Please post any queries in the comments section below. Finally, we  recommend visiting Excelden to learn more about Excel.

(Visited 40 times, 1 visits today)
Sakibul Hasan Nahid

Sakibul Hasan Nahid

Hello I am Sakibul Hasan Nahid. I have completed my BSc. in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo