How to Convert Scientific Notation to Text in Excel (6 Simple Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

excel scientific notation to text


Convert Scientific Notation to Text in Excel: 6 Simple Ways

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:

Dataset to convert scientific notation to text


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.

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

Format Cells toolbox

  • Thus, you can change scientific notations to normal numbers.

Results found after using format cells option

Read More: Convert Scientific Notation to Number in Excel


2. Add a Single Quote Before Scientific Notation

Another easy way to change 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.

Adding single quote before scientific notation

  • Now, the scientific notation is now changed to text.

Changing scientific notation to text by adding single quote

  • Similarly, by adding a single quote (‘) before each number you’ll get a dataset like shown below.

Results found after adding single quote

  • Next, to remove the error, select cell range B5:B9 and right-click on it.
  • Then, click on Ignore Error.

Ignoring error

  • Thus, the error will be removed.

Results after ignoring error

Read More: How to Remove Scientific Notation in Excel


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.

Using TRIM function to convert scientific notation to text

  • Thus, you can convert scientific notation to text using the TRIM function.

Result from TRIM function

In the TRIM function, we inserted Cell B5 as text.

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.

Using UPPER function to convert scientific notation to text

  • Thus, the scientific notation will change into text.

Results found after using UPPER function

Here, in the UPPER function, we inserted Cell B5 as 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.

Using LOWER function to convert scientific notation to text

  • Finally, the scientific notation will change into text.

Results found after using LOWER function

In the LOWER function, we inserted Cell B5 as text.

5. Employ the CONCATENATE Function to Transform Scientific Notation to Text in Excel

To get the 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.

Using CONCATENATE function to convert scientific notation to text

  • Thus, you can convert scientific notation to text using the CONCATENATE function.

Results found after using CONCATENATE function

Here, in the CONCATENATE function, we inserted Cell B5 as text1.

6. Convert Scientific Notation to Text Using the 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.

Using TEXT function to convert scientific notation to text

  • Finally, the scientific notation will change into text.

Results found after using TEXT function

In the TEXT function, we inserted Cell B5 as value, 0 as format_text.

Practice Section

In this section, we are giving you the dataset to practice on your own and learn to use these methods.

Practice Section


Download Practice Workbook

You can download the workbook to practice yourself.


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. Thank you!


Related Articles


<< Go Back to Scientific Notation in Excel | Number Format | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Al Arafat Siddique
Al Arafat Siddique

Al Arafat Siddique, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, has worked on the ExcelDemy project for two years. He has written over 85+ articles for ExcelDemy. Currently, he is working as a software developer. He is leading a team of six members to develop Microsoft Office Add-ins, extending Office applications to interact with office documents. Other assigned projects to his team include creating AI-based products and online conversion tools using the latest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo