Hosne Ara
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.
Download Practice Workbook
You can download the practice workbook that we have used to prepare this article.
7 Methods to Convert the 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. 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.
- As a result, the Format Cells dialog will appear. Now go to the Number tab, select Custom from the number Category, enter 0 in the box: Type. after that press OK.
- Consequently, all the selected exponential numbers will appear as exact numbers.
⏩ Note:
- You can go Home > Number group and then click on the More icon to bring the Format Cells dialog (see screenshot).
Related Content: How to Convert Percentage to Number in Excel (5 Easy Ways)
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)
- 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.
- Finally, you will get the exact number for all the exponential values.
Read More: How to Convert Scientific Notation to Number in Excel (7 Methods)
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)
- Once you hit Enter, excel converts the exponential value to an exact number.
Read More: How to Convert String to Number in Excel VBA (3 Methods)
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)
- Then we will get the length of the specified value which is 14.
- 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,"##############")
- As a consequence, we will get the whole numbers below.
Read More: How to Convert Percentage to Decimal in Excel (7 Methods)
Similar Readings:
- How to Convert Hours and Minutes to Decimal in Excel (2 Cases)
- Convert Degrees Minutes Seconds to Decimal Degrees in Excel
- How to Convert Degrees Decimal Minutes to Decimal Degrees 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 CONCATENATE function returns a single string and luckily excel does not apply exponent to such string.
Steps:
- Type the below formula in Cell C5. Press Enter.
=CONCATENATE(B5)
- Upon entering the formula, we will get the exact numbers as we wanted.
Read More: How to Fix Convert to Number Error in Excel (6 Methods)
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.
- 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.
- Lastly, all the exponential values are converted to exact numbers.
Related Content: How to Bulk Convert Text to Number in Excel (6 Ways)
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.
- Next, go to Data > Text to Columns (Data Tools group).
- As a result, the Text to Columns dialog appears. Select the Original data type: Fixed width and click Next.
- Again click Next.
- Then select the Column data format: Text, choose the destination cell (here, Cell C5), and click Finish.
- Finally, you will get the below result.
Read More: How to Convert Text to Number with Excel VBA (3 Examples with Macros)
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.
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.
