Method 1 – Use Formula to Convert Scientific Notation to x10 to the Power of 3 Format in Excel
1.1 Combine LEFT, TEXT and RIGHT Functions
STEPS:
- Select the cell C5.
- Enter the following formula in the cell C5:
=LEFT(TEXT(B5,"0.00E+0"),3)&"x10^"&RIGHT(TEXT(B5,"0.00E+0"),2)
- Press
- The output of the C5 cell is converted into x10^3 format.
This formula is divided into 2 parts. The Right function takes the value of x10^(). As the cell is referring to the B5 cell, it will take the rightmost 2 digits of the text version of the B5 cell. With the Left function, it takes the value before x10^(). It will take the leftmost 3 digits of the text version of the B5 cell.
- Drag the “fill handle” down to fill all the cells.
- All the scientific notations are converted into x10 to the power of 3 (x10^3) format.
1.2 Apply REPLACE, SEARCH and TEXT Functions Together
STEPS:
- Select cell C5.
- Enter the following formula.
=IF(B5>=1,REPLACE(TEXT(B5,"0.0E+00"),SEARCH("E", TEXT(B5,"0.0E+00")),2,"x10^"), REPLACE(TEXT(B5,"0.0E+00"),SEARCH("E",TEXT(B5,"0.0E+00")),1,"x10^"))
- Press
- The output of the C5 cell is converted into x10^3 format.
The formula is referring to the B5 cell. If the value of the cell is greater than 1, there is no need to consider the symbol of power. So, we are ignoring the sign of exponential (E). But in the false case, when the value is less than 1, we need to consider the sign of exponential.
- Drag the “fill handle” down to fill all the cells.
- All the scientific notations are converted into x10^3 format.
Method 2 – Apply Format Cells Option to Display x10 to the Power of 3 in Excel
STEPS:
- Select cells C5 to C8 in the x10^3 column.
- Click on General of Number group from the ribbon.
- A menu bar will open, select Text.
- The selected cells are converted into Text format.
- Select cell C5.
- Enter the number with exponential in cell C5.
- Enter 50×103 as we want to see 50×10^3.
- Select 3.
- 3 is the power of x10.
- Right-click on the mouse and select Format Cells from the menu.
- A window named Format Cells will open.
- Click on Superscript and do not change the other parameters.
- Press OK.
- Cell C5 is formatted to x10^3 format.
- Do the same formatting for the other scientific notations.
- All the scientific notations are converted into x10^3 format.
Read More: How to Use Excel Exponential Function of Base 10
Method 3 – 3. Insert User-Defined Function to Show x10 to the Power of 3 Format in Excel
STEPS:
- Select Visual Basic from the Developer tab.
- Microsoft Visual Basic for Applications window will open.
- Select Module from the Insert tab.
- Add the following code in the module:
Function Scientific(Value, Power)
X = Value / (10 ^ Power)
Scientific = Format(X, "0.00") + " x 10^" + Str(Power)
End Function
- Return to the dataset.
- Select cell C5.
- Add the following formula in the cell:
=Scientific(B5,3)
- Press
- The scientific notation of B5 cell is converted into x10^3 format in cell C5.
- Drag the “fill handle” down to fill all the cells.
- All the scientific notations are converted into x10^3 format.
Read More: How to Display Exponents in Excel
Download Practice Workbook
Related Articles
- How to Type Exponential in Excel
- How to Use Negative Exponents in Excel
- How to Estimate Inverse Exponential in Excel
- How to Use Exponent Symbol in Excel
<< Go Back to Excel EXP Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!