When the numbers in Excel are too large or too small, you may need to convert them into scientific format. However, the default scientific format in Excel is not in x10 format.
For example, 1,000,000,000 will be formatted into 1.0E+09 when changing it to the scientific format in Excel.
There is no direct way to format the number into x10 format in Excel, please use the following formula to convert into x10 scientific format.
Formula:
=IF(A1>=1, REPLACE(TEXT(A1,"0.0E+00"), SEARCH("E", TEXT(A1,"0.0E+00")), 2, "x10^"), REPLACE(TEXT(A1,"0.0E+00"), SEARCH("E", TEXT(A1,"0.0E+00")), 1, "x10^"))
Where A1 is the cell with the number, and you can change it to the cell with your data.
Explanations:
Step 1: Convert the number in scientific format into the Text format;
| Formula | =TEXT(A1,"0.0E+00") |
|---|---|
| Result | 1.0E+09 |
Step 2: Find the location of letter "E" in the text string. It is the fourth character in this example;
| Formula | =SEARCH("E",TEXT(A1,"0.0E+00")) |
|---|---|
| Result | 4 |
Step 3: Replace the text string "E+" (letter E and plus sign)with new text string "x10^", if the value is greater than or equal to 1;
| Formula | =REPLACE(TEXT(A1,"0.0E+00"), SEARCH("E",TEXT(A1,"0.0E+00")),2, "x10^") |
|---|---|
| Result | 1.0×10^09 |
Step 4: Replace the text string "E" (letter E only) with new text string "x10^", if the value is less than 1.
| Formula | =IF(A1>=1, REPLACE(TEXT(A1,"0.0E+00"), SEARCH("E", TEXT(A1,"0.0E+00")), 2, "x10^"), REPLACE(TEXT(A1,"0.0E+00"), SEARCH("E", TEXT(A1,"0.0E+00")), 1, "x10^")) |
|---|---|
| Result | 1.0×10^09 |