3 Simple Ways to Convert Scientific Notation to Text in Excel

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.

Datasheet for converting excel scientific notation to text

method

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.

Using Format Cells to convert excel scientific notation to text

  • 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.

method

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.

Using quotation to convert excel scientific notation to text

  • 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.

method

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.

Function

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)

Using the TRIM function to convert excel scientific notation to text

  • Following that we press Enter button.
  • Finally, we can see the exact text value of our number.

Function

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)

Using the UPPER function to convert excel scientific notation to text

  • Finally, we press Enter and get the text value of our number.

Function

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)

Using the CONCATENATE function to convert excel scientific notation to text

  • After that, we press Enter button.
  • Finally just like the previous methods we will get the text value in cell C6.
Function

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.

Using the TEXT function to convert excel scientific notation to text

  • 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.

Using the PROPER function to convert excel scientific notation to a number


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.

How to Create Scientific Notation in x 10 Format in Excel


📄 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.

(Visited 15 times, 1 visits today)
Md. Ahaduzzaman

Md. Ahaduzzaman

Hello everyone i'm Md.Ahaduzzaman.I am a civil engineering graduate from BUET.I am enthusiastic and highly motivated to always learn something new to broaden my horizon and find my lacking. Hopefully my Articles will benefit you to understand and overcome your problem.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo