Al Arafat Siddique
In Microsoft Excel, there could be cases where we need to change the Scientific Notation to Text format to show the number in full length. One of the default features of MS Excel is to convert a number into Scientific Notation if it contains more than 11 digits. In this article, we are going to explore the methods available to alter the format to our desired one.
Download Practice Book
Converting Scientific Notation to Text Following 3 Quick Methods
For illustration, we’ll use the following dataset. It shows a number of 19 digits. By default, Excel converts it into Scientific Notation 5.62319E+18. We are going to apply 3 easy methods to this number. Let’s dive in:
Read More: How to Remove Scientific Notation in Excel (4 Quick Ways)
1. Convert Scientific Notation to Text Format in Excel by Cell Formatting
Using the Format Cells function would enable us to select our desired display format. In this method, we changed the Scientific Notation format to our desired format. Follow the steps:
- Here, we see a number in Scientific Notation, 5.62319E+18.
- Now, click the Right button on the cell (B5) that contained the number and select the Format Cells option.Â
- From the Format Cells window, under the Number tab choose the Custom option and type 0 as format code in the input box. Finally, click OK to save the change.
- The following screenshot shows that the scientific notation has been changed to normal numbers.
2. Add a Single Quote Before the NumberÂ
Another easy way for changing Scientific Notation to Text in Excel is to add a single quote ( ` ) before entering the number into the cell.
- Here, in cell C5 enter a single quote ( ` ) and then put 5623185336444640000 and press Enter.
- The scientific notation is now changed to text.
3. Apply a Formula to Convert Scientific Notation to Text in Excel
Microsoft Excel provides several functions that can be used to convert Scientific Notation to Text. In this stage, we’ll apply 4 functions to get our desired result.
3.1 Use of TRIM Function
The first function, we used, is TRIM to change the display format. In the cell C6, put this formula
=TRIM(B6)where B6 contains the number in Scientific Notation.
3.2 Use of UPPER Function
Here in the second function, we used the UPPER function to change the display format. In the cell C6, put this formula
=UPPER(B6)where B6 contains the number in Scientific Notation.
3.3 Use of CONCATENATE Function
To get Scientific Notation to Text format, we used CONCATENATE function as the third function in our formula. In the cell C6, put this formula
=CONCATENATE(B6)where B6 contains the number in Scientific Notation.
3.4Â Use of TEXT Function
As the fourth function, we used TEXT function to change the display format. In the cell C6, put this formula
=TEXT(B6, “0”)where B6 contains the number in Scientific Notation and 0 is the format code.
Conclusion
These methods are definitely going to help you to change the format of a number from Scientific Notation to Text very easily. If there are any queries or suggestions, put them in the comment box below.
