Md. Ahaduzzaman
In many cases, we have to put a long number in an Excel cell. By default, Excel converts the number to scientific notation if the number is longer than eleven digits. But sometimes we need to exactly know the full value stored in the cell. Cases like a database for vehicle registration, bank account number or national id number demand us to store and show the exact number when required. Therefore inside the article, you will find how to convert Scientific notation in Excel to text.
📁 Download Excel File
Download the practice file from here.
Learn to Convert Scientific Notation to Text in Excel Through These 3 Methods
Here in cell B6, we have data entries that have more than 11 digits. So it is shown in scientific notation. We want to know the exact value of that particular cell. For our demonstration purpose, the sample snapshot of our datasheet is as follows.
1. Using Cell Formatting
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we copy our current value in cell C6 where we will change the format.
- After that, we give a right click to our mouse.
- Following that we select Format Cell option.
- After that, we go to the Number category.
- Then we select the Custom category.
- We select type 0.
- Finally, we press OK.
- After doing all these steps we will see that the exact number stored in cell C6 is shown.
2. Adding Single Quote or Apostrophe in Front of a Number
Here we will see how to convert an Excel scientific notation in Excel to text by using single quote. So the step-by-step procedures are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we copy the number in C6.
- After that, we simply put an apostrophe in front of the number and hit Enter.
- Finally, we will notice that the cell value in C6 is now in text format.
Adjusting Column Width for Numbers with 11 Digits or Less
By default, excel converts a number into scientific notation if the digits are more than 11. But sometimes even a number consisting of digits less than 11 can be converted too. It happens if the column width is too short to fit the whole number. In order to avoid this problem we should adjust the column width accordingly so that there is enough space available.
3. Using Excel Functions
Here we will learn the use of 4 Excel functions to convert a scientific notation in Excel to text format. They are TRIM, UPPER, CONCATENATE & TEXT functions.
3.1 Using TRIM Function
Here in this section, we will use the TRIM function to convert excel scientific notation in Excel to text. Hence the step-by-step procedures are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we go to cell C6 and type this formula in the formula box.
=TRIM(B6)
- Following that we press Enter button.
- Finally, we can see the exact text value of our number.
3.2 Using UPPER Function
We will use UPPER function for converting an Excel scientific notation in Excel to text. The steps are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we go to cell C6 and type this formula as mentioned before.
=UPPER(B6)
- Finally, we press Enter and get the text value of our number.
3.3 Using CONCATENATE Function
Here we will demonstrate the CONCATENATE function for converting an Excel scientific notation in Excel to text. The steps are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we go to cell C6 and type this formula in the formula box.
=CONCATENATE(B6)
- After that, we press Enter button.
- Finally just like the previous methods we will get the text value in cell C6.
3.4 Using TEXT Function
Here we will learn the use of the TEXT function to convert an Excel scientific notation in Excel to text. The steps are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we go to cell C6 and type this formula in the formula box and press Enter.
=TEXT(B6,0)
- Here 0 indicates the format code.
- Finally, we will get our converted text value in cell C6.
How to Convert Scientific Notation in Excel to Number
Here we will learn how to convert a scientific notation in Excel to a number using PROPER function. The steps are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- At first, we write this formula in the formula box of cell C6.
=PROPER(B6)
- Following that we press the Enter button.
- Finally, we will see that our scientific notation is converted into number type.
How to Create Scientific Notation in x 10 Format in Excel
Here in this section, we will learn how to convert an Excel scientific notation into x 10 forms. So the step-by-step procedures are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we go to the formula box of cell C6.
- Following that we type this formula in the formula box.
=LEFT(TEXT(B6,”0.00E+0″),3) & “x10^” & RIGHT(TEXT(B6,”0.00E+0″),3)
🔨 Formula Breakdown
👉 Here TEXT(B6,”0.00E+0″) converts the value B6 in the specific number format.
👉 LEFT(TEXT(B6,”0.00E+0″),3) returns the first three characters from the left which is 1.2
👉 RIGHT(TEXT(B6,”0.00E+0″),3) returns the last three characters from the right which is +16
👉 Finally we write the three parts together using & operation.
📄 Important Notes
🖊️ While using the Excel functions, we must be careful about the function argument.
🖊️ We can use either cell formatting or Excel Functions to convert scientific notation in Excel to text.
📝 Takeaways from This Article
📌 We can use cell formatting to convert an Excel scientific notation in Excel to text.
📌 We can also use a single quote for this purpose.
📌 Apart from these we can use the functions to convert scientific notation in Excel to text.
Conclusion
Here we have learned three easy ways to convert excel scientific notation to text in Excel. We can use either the cell formatting menu or we can use different Excel functions like TRIM, UPPER, CONCATENATE & TEXT Functions. We can use any of the methods we like. Users can comment in the comment box if they have any queries regarding this article. For further Excel-related solutions, you can visit our website Excelden.
