How to Convert Exponential Value to Exact Number in Excel (7 Methods)

Method 1 – Using Cell Formatting to Convert an Exponential Value to an Exact Number

We have a dataset (B5:B8) containing several exponential values that we want to display as exact numbers.

Steps:

• Select the dataset and right-click on it.
• Click on Format Cells.

• The Format Cells dialog will appear.
• Go to the Number tab, select Custom from the number Category, and enter 0 in the Type box.
• Click OK.

• All the selected exponential numbers will appear as exact numbers.

⏩ Note:

• You can click Home and then click on the More icon in the Number group to access the Format Cells dialog (see screenshot).

Read More: How to Convert European Number Format to US in Excel

Method 2 – Using the TRIM Function to Convert an Exponential Value to an Exact Number

Steps:

• Input the following formula in cell C5 and press Enter:

• We will get the exact number in cell C5.
• Use the Fill Handle (+) tool to copy the formula to the rest of the desired cells.

• We will get the exact number for all the exponential values.

Method 3 – Using the UPPER Function to Convert an Exponential Value to an Exact Number

Steps:

• Enter the following formula in Cell C5.

• Hit Enter and use AutoFill.

Method 4 – Using the TEXT Function to Convert an Exponential Value to an Exact Number

The syntax of the function is:

TEXT(value,format_text)

Before applying the TEXT function, we will use the LEN function to get the length of the exponential value. Based on the length of the value, we will apply the hash ‘#’ symbol in the second argument of the TEXT function to get the exact number. We’ll apply this to a list of exponential values which are of similar length.

Steps:

• Add the formula below to cell C5 and hit Enter.

• We will get the length of the specified value, which is 14.

• Since the length of the exponential value is 14, we need to insert 14 hash signs ‘#’ in the TEXT formula.
• Add the following formula to Cell D5 and press Enter.

• Use AutoFill. If the lengths are not the same, AutoFill the Length column and then change the formula accordingly.

Read More: How to Convert Percentage to Decimal in Excel

Method 5 – Using the CONCATENATE Function to Convert an Exponential Value to an Exact Number

Steps:

• Insert the formula below to cell C5.

• Press Enter.

• Use AutoFill.

Method 6 – Using a Text Format with an Apostrophe to Convert an Exponential Value to an Exact Number

Steps:

• Double-click on the first cell of the list and put the cursor at the beginning of the value.
• Add an apostrophe.

• Hit Enter.
• Use the Fill Handle tool to get the exact number in the rest of the cells.

Method 7 – Using the Text to Columns Option to Convert an Exponential Value to an Exact Number

Steps:

• Select the range B5:B8.

• Go to Data and select Text to Columns (in the Data Tools group).

• In the Text to Columns dialog box, select the Original data type: Fixed width and click Next.

• Click Next again.

• For Column data format, choose Text, choose the destination cell (here it’s Cell C5), and click Finish.

• Here’s the result.

Things to Remember

• You can apply the Number format in cells instead of General to show large numbers as full text.
• A cell’s number format does not affect the actual value. We can see the actual number in the Excel Formula Bar.

Download the Practice Workbook

Related Articles

• [Solved] Excel Number Stored As Text
  
• Number Format Is Not Working in Excel
• [Solved!] Long Numbers Are Displayed Incorrectly in Excel
  

<< Go Back to Change Number Format | Number Format | Learn Excel