# How to Apply Engineering Number Format in Excel – 2 Methods

This is an overview:

The dataset showcases Sample ID, Preferable Weather, and Reproduction Time (Sec).To show the values of Reproduction Time (Sec) in engineering number format:

### Method 1 – Utilizing Custom Number Format to Apply Engineering Number Format

Steps:

• Select D5:D14.

• Press Ctr+1.
• The Format Cells dialog box is displayed.

• In Number, select Custom.
• Select ##0.0E+0 in Type:.
• Click OK.

Tips: You can customize the number of decimal places.

Here, more zeros were entered to control decimal places.

This is the output.

### Method 2 -Using the TEXT Function to Get Values in Engineering Number Format

Steps:

• Select E5.
• Enter the following formula:
`=TEXT(D5,"##0.0E+0")`

Formula Breakdown

• TEXT(D5,”##0.0E+0″)The TEXT function converts the value in D5 into engineering format ##0.0E+0.
• Output: 7E-6.

• Drag down the Fill Handle to see the result in the rest of the cells.

## How to Display Values with Units in Engineering Format in Excel?

• Create a Result column to show values in prefixes.

• Select E5.
• Use 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 converts the value in D5  into  engineering format ##0.0E+0.
• Output: 7E-6.
• FIND(“E”,TEXT(D5,”##0.0E+0″)) → becomes
• FIND(“E”, “98.7E-6”) The FIND function locates E in the string 7E-6 and returns its position.
• Output5.
• LEFT(TEXT(D5,”##0.0E+0″),FIND(“E”,TEXT(D5,”##0.0E+0″))-1) → becomes
• LEFT(“98.7E-6”,4) The LEFT function returns 4 digits starting from the left of the provided string 7E-6.
• Output7.
• 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 return characters starting from position 6.
• Output-6.
• VALUE(MID(TEXT(D5,”##0.0E+0″),FIND(“E”,TEXT(D5,”##0.0E+0″)) → becomes
• VALUE(“-6”) The VALUE function converts the text -6 into a number.
• Output-6.
• 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 returns one character from position 3. (here, nnano, ppico, umicro, mmili, k kilo, MMega, GGiga, TTera is in npum kMGT)
• Outputu.
• 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 returns the combined form of the given values.
• Output7u.
• 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 returns the combined form of the given values.
• Output7u s.

• Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

Practice here.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Rabeya Islam

Rabeya Islam, BSc, Computer Science and Engineering, East West University, Bangladesh, has been working with the SOFTEKO for more than one and half years. She has written some articles for ExcelDemy. Currently, she is working as the team leader, oversees the day-to-day work, and leads the SQA team Excel Extensions project. She has built the workflow and the structure of the extension testing for the team. Her work and learning interests vary from Microsoft Office Suites, VBA, and... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF