This is an overview:
The following dataset contains 5 numbers of more than 11 digits. By default, Excel converts them into Scientific Notation.
Method 1 – Use the Format Cells Option to Convert Scientific Notation to Text
Steps:
- Select B5:B9.
- Right-click and select Format Cells.
- In Number, choose Custom.
- Select 0 in Type:.
- Click OK.
- This is the output.
Read More: Convert Scientific Notation to Number in Excel
Method 2 – Add a Single Quote Before the Scientific Notation
Steps:
- In B5, enter a single quote (‘) before the number.
- Press Enter.
Scientific notation changed to text.
- Add a single quote (‘) before each number:
- To remove the error, select B5:B9 and right-click.
- Click Ignore Error.
The error will be removed.
Read More: How to Remove Scientific Notation in Excel
Method 3 – Using the TRIM Function to Convert Scientific Notation to Text in Excel
Steps:
- Enter the following formula in C5.
=TRIM(B5)- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
This is the output.
In the TRIM function, B5 is added as text.
Method 4 – Convert Scientific Notation to Text Using the UPPER or the LOWER Functions
Steps:
- Enter the following formula in C5.
=UPPER(B5)- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
This is the output.
In the UPPER function, B5 is added as text.
- To use the LOWER function, enter the following formula in B5.
=LOWER(B5)- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
This is the output.
In the LOWER function, B5 is added as text.
Method 5 – Using the CONCATENATE Function to Transform Scientific Notation to Text in Excel
Steps:
- Enter the following formula in C5.
=CONCATENATE(B5)- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
This is the output.
In the CONCATENATE function, B5 is added as text1.
Method 6 – Convert Scientific Notation to Text Using the TEXT Function
Steps:
- Enter the following formula in C5.
=TEXT(B5,"0")- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
This is the output.
In the TEXT function, B5 is added as value, 0 as format_text.
Practice Section
Practice here.
Download Practice Workbook
Download the workbook.
Related Articles
- How to Set Scientific Notation to Powers of 3 in Excel
- Enter Scientific Notation in Excel
- Prevent Excel from Converting to Scientific Notation CSV
- Excel Engineering Number Format
- Excel Scientific Notation Without e
<< Go Back to Scientific Notation in Excel | Number Format | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
