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

In this tutorial, I will show you how to convert an exponential value to an exact number in Excel. Usually, when you enter a number that is longer than 11 digits, excel automatically shows that as an exponential value. However, while working in Excel, you find these exponential values annoying as you cannot see the whole number. So, let’s see how we can convert exponential values to exact numbers using several functions and options in Excel.

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

1. Convert Exponential Value to Exact Number Using Cell Formatting

Let’s assume we have a dataset (B5:B8) containing several exponential values that we want to be displayed 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 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 Exponential Value to Exact Number in Excel

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 Exponential Value to Exact Number in Excel

Steps:

• Enter the following formula in Cell C5.

• Hit Enter, and Excel converts the exponential value to an exact number.

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

We know that the TEXT function converts a value to text in a specific number format. The syntax of the function is:

TEXT(value,format_text)

Before applying the TEXT function, I will use the LEN function to know the length of the exponential value. Based on the length of the value, I 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, insert 14 hash ‘#’ in the TEXT formula. Add the following formula to Cell D5 and press Enter.

• We will get the whole numbers below.

Read More: How to Convert Percentage to Decimal in Excel

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

The CONCATENATE function returns a single string and Excel does not apply exponents to such a string.

Steps:

• Add the formula below to Cell C5. Press Enter.

• We will get the exact numbers.

Method 6 – Using Text Format with Apostrophe to Convert Exponential Value to Exact Number in Excel

If you add an apostrophe to the beginning of a large number in Excel, Excel will not convert the number to an exponential value. This can also be applied to a list of exponential values.

Steps:

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

• Hit Enter, and we will get the result below.
• 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 Exponential Value to Exact Number in Excel

Steps:

• Select the range (B5:B8).

• Go to Data > Text to Columns (Data Tools group).

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

• Click Next again.

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

• We will get the result below.

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 Practice Workbook

You can download the practice workbook that we have used to prepare this article.

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