Scientific or engineering work must show the number format differs from other forms. As it makes scientists work more efficiently, it also helps them to understand digits accordingly. In this article, I will show how to employ the engineering number format in Excel.
Employing Engineering Number Format in Excel: 2 Useful Methods
I have a dataset of a lab, where the attributes are Sample ID, Preferable Weather, and Reproduction Time (Sec). In this dataset, I will show the values of Reproduction Time (Sec) in engineering number format using engineering number format. In this tutorial, I used the Microsoft Excel 365 version.
1. Utilizing Custom Number Format to Apply Engineering Number Format
You can create a custom number format to display the numbers or digits in engineering number format. I will explain the method in some easy steps.
Steps:
- Select the cells D5:D14 to format.
- Press Ctr+1.
- A dialog box of Format Cells will appear.
- From the Number tab, Select Custom. Then, Select ##0.0E+0 from the type list.
- Click OK.
Here, I typed more zeros to control the decimal points.
- The format will now look like the image below.
Read More: Convert Scientific Notation to Text in Excel
2. Implementing TEXT Function to Get Values with Engineering Number Format
You can simply implement the TEXT function to display the values in engineering format.
Steps:
- Select cell E5.
- Type the following formula:
=TEXT(D5,"##0.0E+0")
Formula Breakdown
- TEXT(D5,”##0.0E+0″) → The TEXT function will convert the value from the D5 cell into the given engineering format ##0.0E+0.
- Output: 7E-6.
- Now, Fill the other cells using the Autofill Feature.
Read More: Convert Scientific Notation to Number in Excel
How to Display Values with Units from Engineering Formatted Numbers in Excel?
You can also display the numbers in prefixes if you need to. In this section, I will explain how to display values in prefixes and with units after digits.
- Create a column Result to show values in prefixes.
- Select cell E5.
- Type the following formula:
=LEFT(TEXT(D5,"##0.0E+0"),FIND("E",TEXT(D5,"##0.0E+0"))-1) & MID("npum kMGT",5+VALUE(MID(TEXT(D5,"##0.0E+0"),FIND("E",TEXT(D5,"##0.0E+0"))+1,100))/3,1) &" s"
Formula Breakdown
- TEXT(D5,”##0.0E+0″) → The TEXT function will convert the value from the D5 cell into the given engineering format ##0.0E+0.
- Output: 7E-6.
- FIND(“E”,TEXT(D5,”##0.0E+0″)) → becomes
- FIND(“E”, “98.7E-6”) → The FIND function will try to locate E in the string 7E-6 and return its location.
- Output → 5.
- FIND(“E”, “98.7E-6”) → The FIND function will try to locate E in the string 7E-6 and return its location.
- LEFT(TEXT(D5,”##0.0E+0″),FIND(“E”,TEXT(D5,”##0.0E+0″))-1) → becomes
- LEFT(“98.7E-6”,4) → The LEFT function will return 4 digits starting from the left of the provided string 7E-6.
- Output → 7.
- LEFT(“98.7E-6”,4) → The LEFT function will return 4 digits starting from the left of the provided string 7E-6.
- MID(TEXT(D5,”##0.0E+0″),FIND(“E”,TEXT(D5,”##0.0E+0″))+1,100) → becomes
- MID(“98.7E-6”,6,100) → The MID function will return characters starting from position 6.
- Output → -6.
- MID(“98.7E-6”,6,100) → The MID function will return characters starting from position 6.
- VALUE(MID(TEXT(D5,”##0.0E+0″),FIND(“E”,TEXT(D5,”##0.0E+0″)) → becomes
- VALUE(“-6”) → The VALUE function will convert the text -6 into a number.
- Output → -6.
- VALUE(“-6”) → The VALUE function will convert the text -6 into a number.
- MID(“npum kMGT”,5+VALUE(MID(TEXT(D5,”##0.0E+0″),FIND(“E”,TEXT(D5,”##0.0E+0″))+1,100) → becomes
- MID(“npum kMGT”,3,1) → The MID function will return one character from position 3. (here, n → nano, p → pico, u → micro, m → mili, k → kilo, M → Mega, G → Giga, T → Tera is in npum kMGT)
- Output → u.
- MID(“npum kMGT”,3,1) → The MID function will return one character from position 3. (here, n → nano, p → pico, u → micro, m → mili, k → kilo, M → Mega, G → Giga, T → Tera is in npum kMGT)
- LEFT(TEXT(D5,”##0.0E+0″),FIND(“E”,TEXT(D5,”##0.0E+0″))-1) & MID(“npum kMGT”,5+VALUE(MID(TEXT(D5,”##0.0E+0″),FIND(“E”,TEXT(D5,”##0.0E+0″))+1,100))/3,1) → becomes
- “98.7” &”u” → The & operator will return the combined form of the given values.
- Output → 7u.
- “98.7” &”u” → The & operator will return the combined form of the given values.
- LEFT(TEXT(D5,”##0.0E+0″),FIND(“E”,TEXT(D5,”##0.0E+0″))-1) & MID(“npum kMGT”,5+VALUE(MID(TEXT(D5,”##0.0E+0″),FIND(“E”,TEXT(D5,”##0.0E+0″))+1,100))/3,1) &” s” → becomes
- “98.7u” &” s” → The & operator will return the combined form of the given values.
- Output → 7u s.
- “98.7u” &” s” → The & operator will return the combined form of the given values.
- Now, Fill the other cells using the Autofill Feature.
- The final look of the values will be like the following image.
Here, all the values are shown in the prefixes and also the units are shown at the end of the values.
Practice Section
On the right side of each worksheet, you will find a practice section to practice on your own.
Download Practice Workbook
You can download the practice workbook here.
Conclusion
In this article, you can learn how to show values in engineering number format in Excel according to your needs. Feel free to give your thoughts about this article in the comment section below.
Related Articles
- Enter Scientific Notation in Excel
- How to Display Power in Excel
- How to Set Scientific Notation to Powers of 3 in Excel
- How to Remove Scientific Notation in Excel
- Prevent Excel from Converting to Scientific Notation CSV
- Excel Scientific Notation Without e
<< Go Back to Scientific Notation in Excel | Number Format | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
