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

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Convert Exponential Value to Exact Number in Excel: 7 Methods

1. Convert Exponential Value to Exact Number Using Cell Formatting

Let’s assume we have a dataset (B5:B8) containing several exponential values. Now I will change the Number format of the corresponding cells by applying cell formatting.

Steps:

  • First, select the dataset and right-click on it. Then click on Format Cells.

Convert Exponential Value to Exact Number Using Cell Formatting

  • As a result, the Format Cells dialog will appear. Now go to the Number tab, select Custom from the number Category, and enter 0 in the box: Type. after that press OK.

Convert Exponential Value to Exact Number Using Cell Formatting

  • Consequently, all the selected exponential numbers will appear as exact numbers.

Convert Exponential Value to Exact Number Using Cell Formatting

Note:

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

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


2. Excel TRIM Function to Turn Exponential Value to Exact Number

We can use several Excel functions to convert exponential values to exact numbers. First of all, I will use the TRIM function in Excel to do the task.

Steps:

  • Type the below formula in Cell C5 and press Enter from the keyboard.
=TRIM(B5)

Excel TRIM Function to Turn Exponential Value to Exact Number

  • Upon entering the formula, we will get the below exact number. Use the Fill Handle (+) tool to copy the formula to the rest of the cells.

Excel TRIM Function to Turn Exponential Value to Exact Number

  • Finally, you will get the exact number for all the exponential values.


3. Apply UPPER Function to Change Exponential Value to Exact Number

This time, I will use the UPPER function to turn the exponential values into whole numbers.

Steps:

  • Type the following formula in Cell C5.
=UPPER(B5)

Apply UPPER Function to Change Exponential Value to Exact Number

  • Once you hit Enter, excel converts the exponential value to an exact number.


4. Turn Exponential Value to Exact Number with Excel TEXT Function

Now, I will use the TEXT function 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)

Here, before applying the TEXT function, I will use the LEN function to know the length of the exponential value. Later, 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. Let’s assume a list of exponential values which are of similar length.

Steps:

  • First, type the below formula in Cell C5 and hit Enter.
=LEN(B5)

Turn Exponential Value to Exact Number with Excel TEXT Function

  • Then we will get the length of the specified value which is 14.

Turn Exponential Value to Exact Number with Excel TEXT Function

  • Now as the length of the exponential value is 14, I will insert 14 hash ‘#’ in the TEXT formula. So, type the following formula in Cell D5 and press Enter.
=TEXT(B5,"##############")

Turn Exponential Value to Exact Number with Excel TEXT Function

  • As a consequence, we will get the whole numbers below.

Read More: How to Convert Percentage to Decimal in Excel


5. Change Exponential Value to Exact Number Using CONCATENATE Function

You can get an exact number using the CONCATENATE function in Excel. We know the CONCATENATE function returns a single string and luckily excel does not apply exponent to such a string.

Steps:

  • Type the below formula in Cell C5. Press Enter.
=CONCATENATE(B5)

Change Exponential Value to Exact Number Using CONCATENATE Function

  • Upon entering the formula, we will get the exact numbers as we wanted.


6. Convert Exponential Value to Number as Text Format with Apostrophe

While entering a large number in Excel, if you put an apostrophe at the beginning of the number excel will not convert the number as an exponential value. Besides, you can convert a list of exponential values to exact numbers just by putting an apostrophe.

Steps:

  • Firstly, double-click on the first cell of your list and put the cursor at the beginning of your value. Then put an apostrophe there.

Convert Exponential Value to Number as Text Format with Apostrophe

  • Hit Enter and you will get the below result. After that, use the Fill Handle tool to get the exact number in the rest of the cells.

Convert Exponential Value to Number as Text Format with Apostrophe

  • Lastly, all the exponential values are converted to exact numbers.


7. Use Text to Columns Option to Get Exact Number from Exponential Value

I will show you using the Text to Columns option of Excel to turn exponential values into integers.

Steps:

  • Select the range (B5:B8) at first.

Use Text to Columns Option to Get Exact Number from Exponential Value

  • Next, go to Data > Text to Columns (Data Tools group).

Use Text to Columns Option to Get Exact Number from Exponential Value

  • As a result, the Text to Columns dialog appears. Select the Original data type: Fixed width and click Next.

Use Text to Columns Option to Get Exact Number from Exponential Value

  • Again click Next.

Use Text to Columns Option to Get Exact Number from Exponential Value

  • Then select the Column data format: Text, choose the destination cell (here, Cell C5), and click Finish.

Use Text to Columns Option to Get Exact Number from Exponential Value

  • Finally, you will get the below result.


Things to Remember

➤ You can apply the Number format in cells instead of General to show large numbers as full text.

➤ 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.


Conclusion

In the above article, I have tried to discuss several methods to convert the exponential value to an exact number in Excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Hosne Ara
Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo