How to Convert Scientific Notation to Text or Number in Excel?
Last Updated : 07 Mar, 2022
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.
Similar Reads
How To Convert Date To Number String Or Text Format In Excel?
Excel's built-in date system by default supports dates between January 1, 1900, and December 31, 9999. In this range, the numbers start at 1 and increase by 1 each time. In addition to text and numeric data types, Excel also allows dates that are internally stored as serial numbers. In Excel, the da
4 min read
How to write numbers in scientific notation?
To write numbers in scientific notation, place the decimal after the first digit, count the shifts, and express as a product of that number and 10 raised to the shift's power.For example, 5000 can be written as 5×103 because the decimal point moves three places to the left, and 0.0098 can be written
3 min read
How to prevent scientific notation in R?
Scientific notations in R Programming Language are considered equivalent to the exponential formats. Scientific notations are used for numbers that are extremely large or small to be handled in the decimal form. In R Language, the E-notation is used for such numbers. In this article, we are going to
3 min read
How to Convert Numeric Dataframe to Text in xlsx File in R
In this article, we are going to convert a numeric data frame which means the data present in both rows and columns of the data frame is of numeric type to an Excel file(.xlsx). To achieve this mechanism in R Programming, we have a package called writexl which contains the write_xlsx() function whic
2 min read
How to Convert Date to Numeric in R?
In this article, we will discuss how to convert date to numeric in R Programming Language. Method 1: Using as.numeric() This function is used to convert date into numeric Syntax: as.numeric(date) where the date is the input date. Example: [GFGTABS] R data = as.POSIXct("1/1/2021 1:05:00 AM"
2 min read
How to Simplify Numbers Using Scientific Notation?
Simplifying numbers using scientific notation involves expressing large or small numbers in a compact form, which makes calculations and comparisons easier.Scientific notation is a method used to represent very large or very small numbers in a more concise format. The exponent or power of a number r
7 min read
How to Convert Text to Date in Excel
Have you ever found yourself staring at a column of dates in Excel, only to realize they’re formatted as text? It's a common problem that can hinder your data analysis and reporting tasks. Fortunately, converting text to date in Excel is straightforward. If you're looking to use the Text to Columns
5 min read
How to convert a String into Number in PHP ?
Strings in PHP can be converted to numbers (float/ int/ double) very easily. In most use cases, it won't be required since PHP does implicit type conversion. This article covers all the different approaches for converting a string into a number in PHP, along with their basic illustrations. There are
4 min read
How to Put Count and Percentage in One Cell in Excel?
In the real world, sometimes the user would like to present two or more values in a single cell. For eg in the sales report, we might need sales and the respective %share in the same cell. So in this article, we will learn how to display count and percentage in the same cell with the help of an exam
2 min read
How to Correctly Sum Numbers With Units in a Range in Excel?
In Excel, we can sum numbers in a range which are given along with their units. When we place numbers along with their units inside Excel then they are treated as text by Excel implicitly in the backend. Now, the problem that arises here is we cannot directly apply any mathematical function like the
3 min read