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 Workbook
You can download the workbook to practice yourself.
6 Simple Ways to Convert Scientific Notation to Text in Excel
For illustration, we’ll use the following dataset. It shows 5 numbers of more than 11 digits. By default, Excel converts it into Scientific Notation. We are going to apply 6 simple methods to this number. Let’s dive in:
1. Use Format Cells Option to Convert Scientific Notation to Text
The Format Cells option 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 given below.
Steps:
- Firstly, select cell range B5:B9.
- Then, right-click on it and select the Format Cells option.
- Now, from the Format Cells dialog box, under the Number tab choose the Custom option and select 0 as format code.
- Finally, click OK to save the change.
- Thus, you can change scientific notations to normal numbers.
2. Add Single Quote Before Scientific Notation
Another easy way for changing Scientific Notation to Text in Excel is to add a single quote (‘) before entering the number into the cell.
Steps:
- In the beginning, in Cell B5 insert a single quote (‘) before the number and press Enter.
- Now, the scientific notation is now changed to text.
- Similarly, adding a single quote (‘) before each number you’ll get a dataset like shown below.
- Next, to remove the error, select cell range B5:B9 and right-click on it.
- Then, click on Ignore Error.
- Thus, the error will be removed.
3. Apply TRIM Function to Convert Scientific Notation to Text in Excel
Next, we will use the TRIM function to change the display format. Here are the steps to do that.
Steps:
- Firstly, type the following formula in Cell C5.
=TRIM(B5)- Then, press Enter and you’ll get the scientific number to text.
- After that, apply the same formula in all other cells, just drag the Cell C5.
- Thus, you can convert scientific notation to text using the TRIM function.
4. Convert Scientific Notation to Text Using UPPER or LOWER Functions
You can also use the UPPER or LOWER functions to convert scientific notation to text in excel.
Steps:
- To start with, insert the following formula in Cell C5.
=UPPER(B5)- After that, press Enter and drag down the Fill Handle tool to copy the formula for the rest of the cells.
- Thus, the scientific notation will change into text.
- However, to apply the LOWER function type the following formula in Cell B5.
=LOWER(B5)- Now, press Enter and drag down the Fill Handle tool to copy the formula for the rest of the cells.
- Finally, the scientific notation will change into text.
5. Employ CONCATENATE Function Transform Scientific Notation to Text in Excel
To get Scientific Notation to Text format, we will now use the CONCATENATE function.
Steps:
- Firstly, type the following formula in Cell C5.
=CONCATENATE(B5)- Then, press Enter and you’ll get the scientific number to text.
- After that, apply the same formula in all other cells, just drag the Cell C5.
- Thus, you can convert scientific notation to text using the CONCATENATE function.
6. Convert Scientific Notation to Text Using TEXT Function
Lastly, we will use the TEXT function to change the display format. Here are the steps to do that.
Steps:
- In the beginning, insert the following formula in Cell C5.
=TEXT(B5,"0")- After that, press Enter and drag down the Fill Handle tool to copy the formula for the rest of the cells.
- Finally, the scientific notation will change into text.
Practice Section
In this section, we are giving you the dataset to practice on your own and learn to use these methods.
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. And visit ExcelDemy for many more articles like this. Thank you!
Read More: How to Remove Scientific Notation in Excel (4 Quick Ways)
