How to Convert Scientific Notation to Text or Number in Excel?

Microsoft Excel is a universal software tool that is used by companies throughout the world to store data/numbers in the form of a spreadsheet. Excel not only helps users to store the data into spreadsheets, but it also gives us the freedom to use multiple smart features to manipulate, organize and analyze the raw data. Some of these helpful smart features also create an issue in a few cases. In this article, we are going to read about one such issue and how we can resolve that issue. But, before we go on to identify the issue and resolve it let’s understand how Excel generally treats data and stores it inside the cells. 

Convert Scientific Notation To Text Or Number In Excel

Suppose, we want to store the salary of five people in an Excel spreadsheet. It can be done as:

Salary record of five employees in a company

If we carefully observe the picture shown above, we can notice, all the cell constituents like “Employee Name”, “Ram”, “Rajni”, “Sameer”, etc. are identified by Excel as text and are generally stored with left alignment inside the cell whereas if we notice constituents like 50000, 75000, 45000, etc. Excel automatically understands and treats them as numbers and all of them are stored in the cell with the right alignment. From this, we can observe the general convention that text is placed with left alignment and numbers are placed with right alignment inside the cell. Now, that we have a basic understanding of the convention we are good to progress towards our original issue of the article.

This issue arises when we try to store large value numbers inside a cell in an excel spreadsheet. Excel identifies the number and automatically converts it into scientific notation. Let’s take an example of a number: 111122223333444, when we input this number inside the cell and hit enter it will be displayed as shown in the figure given below:

A large valued number entered inside the cell

Now, sometimes these scientific notations can be confusing to us and we want to view our records in the original format. We can have a workaround it and we can restore the original formatting using the methods discussed below:

Method 1: By using the Format cells dialog box option

This method can be used for viewing the original form in case of single as well as multiple cells of data. 

Step 1: We need to select the data and then right-click on the mouse to see different options, from the options we can select the Format cells option to open the format cells dialog box.

Select the cells and right-click on the mouse button to see available options 

Step 2: Upon clicking, the format cell dialog box appears, on the top make sure the tab selected is Number and then on the left-hand side, we can observe the Category window.

Step 3: In the category window, click on the Custom option, now in this option we can choose the desired format in which we want our number to be displayed. The box also enables us to view the sample in that window option which signifies what our data will look like after finalizing the result.

Step 4: Now, click OK to finalize our selection. As soon as we click OK, we can see all our selected cells will so the numbers as it is in their natural form.

A video is also attached below demonstrating all the above-given steps:

Method 2: By using in-built TRIM() function in Excel

Excel has an in-built TRIM() function which is generally used with text to remove whitespaces in them. We can even use this function to convert numbers in scientific notation back to their original form. We can pass our number as a parameter inside the TRIM() function and it returns our original number in output without converting it into scientific notation. But the catch here is that the TRIM() function treats its output as a text, and hence when it returns the value the result we get will be a text and not a number. Remember, the usual data storing convention that we studied earlier in this tutorial. We can notice, all the numbers in our result are placed in the cells that have left alignment which signifies Excel now treats these numbers as text.

Passing the number as a parameter inside the TRIM() function

A video is also attached below demonstrating all the above-given procedures:

Method 3: Making use of an apostrophe symbol

We can insert a large number in the cell, just by placing an apostrophe sign (‘) before entering the actual number. This method stops Excel from automatically treating the entered value as a number and it will be stored in the cell as a text which can be confirmed by noticing the left alignment of the record inside the cell.

Using an apostrophe symbol before the number

A video is also attached below demonstrating all the above-given procedures:

Thus, we can use any of these three methods discussed above to convert numbers from scientific notation back to text or numbers in excel as per our requirements.

saurabh48782

Follow

Improve

Article Tags :

• Excel
• Geeks Premier League
• Microsoft Office
• News
• Excel-functions
• ExcelGuide
• Geeks-Premier-League-2022

+2 More