Md. Abdul Kader
Once you input any data for example a number, it will be stored typically as a number. More importantly, if the number of digits is greater than 15, your input number might change and be stored in scientific notation. But in some specific cases, we need to store numbers as texts excluding the scientific notation for searching the data part by part, matching two cells, better visualization, or any other reasons. But how can we do that? In this article, I am going to discuss the 7 methods to convert any number to text without scientific notation in Excel in a super-fast manner.
Download Practice Workbook
7 Methods to Convert Number to Text without Scientific Notation in Excel
In the following dataset, the population of the top 10 most populated states, adopted from World Population Review, in number format is provided in the C5:C14 cell range. If you want to express it in scientific notation using the Format Cells option, it will look like in the D5:D14 cell range. The main task of today’s article is to convert the number to text without scientific notation.
Let’s execute the task.
1. Using the TEXT Function to Convert Number to Text without Scientific Notation
In the beginning method, I’ll show you the application of the TEXT function. The function converts a value to text in a specified format.
To use this function, insert the below formula in the E5 cell.
=TEXT(D5,"0")
Here, D5 is the starting cell of the population with scientific notation and 0 is the format code that shows all insignificant zeros. That’s why the output will act as a text.
After inserting the formula, just press ENTER and drag down the plus sign located at the lower-right corner of the E5 cell (Fill Handle Tool). Immediately, you’ll get the output (E5:E14 cell range).
Read More: How to Convert Numbers to Texts/Words in Excel
2. Applying the CONCATENATE Function
Besides, you may accomplish the same task using the CONCATENATE function which combines multiple strings to a single string. So, the formula will be-
=CONCATENATE(D5)
Read More: Excel VBA: Convert Number to Text with Format (A Total Guide)
3. Using the TRIM Function
Similarly, the TRIM, one of the text functions, will provide you with the same output. Because it stores numbers in text format as well as removes unnecessary spaces from the text string. The adjusted formula in the E5 cell is-
=TRIM(D5)
Read More: How to Convert Number to Text with Commas in Excel (3 Easy Methods)
Similar Readings:
- How to Convert Number to Text and Keep Trailing Zeros in Excel (4 Ways)
- Convert Number to Text for VLOOKUP in Excel (2 Ways)
4. Utilizing the UPPER Function to Convert Number to Text without Scientific NotationÂ
This is the last function of today’s article that you might utilize to convert numbers to text excluding scientific notation. And the UPPER function mainly converts a text string to all uppercase letters. Interestingly, you can use it here as it will store in text format if you input any number with the function. Let’s use the formula in the E5 cell.
=UPPER(D5)
Related Content: How to Convert Number to Text with 2 Decimal Places in Excel (5 Ways)
5. Adding a Single Quote
Also, there are other methods to convert number to text without scientific notation except using the Excel function. According to this method, you have to add only a Single Quote or Apostrophe (‘) before the number. Thus, you may easily store the number as text. So, you must input the following things in the corresponding formula bar.
‘Any number that you want to convert
If I take the example for the D5 cell, you have to insert-
'39613493
After inserting in the above manner, if you press ENTER, you’ll get the output but with the green triangle in the upper-left corner showing an error in the cell. To remove the error, just click on the triangle and choose the Ignore Error option.
Therefore, you’ll see the number in text format without error. Then, you may repeat the same thing i.e. adding a single quote and inserting the number. Obviously, it’ll be a tedious and time-consuming task. Rather, you may use the Flash Fill tool to apply the same format for the below cells. For doing this, just insert a single quote in the E6 cell, and you’ll automatically see the preview to fill (or press CTRL + E to turn on the tool).
If you click on the last cell of the preview (E14 cell), you’ll get the following output.
Related Content: Excel VBA to Convert Number to Text (4 Examples)
6. Applying the Format Cells Option
Surely, you might hear about the Format Cells option. Luckily, you can simply apply the option to convert numbers to text.
To do this, just copy the D5:D14 cell range by pressing CTRL + C and paste the copied cells by pressing CTRL + V in the E5 cell.
Then, open the Format Cells option by using the CTRL + 1 keyboard shortcut after selecting the E5:E14 cell range. And choose the Text option from the Number tab as shown in the following dialog box. Finally, press OK.
Shortly, you’ll get your desired output stored in text format.
Related Content: How to Convert Text to Numbers in Excel
7. Using Text to Column Feature to Convert Number to Text without Scientific Notation
Lastly, you may use the Text to Columns feature to convert the number to text as well as remove the scientific notation.
Initially, copy the data and paste it into the E5:E14 cell range. While selecting the data, click on the Text to Column feature from the Data Tools ribbon in the Data tab.
Next, you’ll see a dialog box. Select Fixed width. Click on the Next button.
Again, click on the Next button.
In Step 3 of 3, you must check the circle before the Text format and click on Finish.
Eventually, you’ll get the following output.
Related Content: How to Convert Number to Text with Green Triangle in Excel
Quick Notes
Checking Whether a Cell Contains Text or Not
If you want to check whether your converted output is in text format or not, you may apply the ISTEXT, one of the IS functions in Excel.
=ISTEXT(D5)
The ISTEXT function returns TRUE if it finds text in the cell. Else it will return FALSE.
After pressing ENTER and using the Fill Handle Tool, the output will look like the following.
The above picture clearly reveals that the output is a text format.
Note: Here, I took the output found using the TEXT function as an example. You may check the output from the rest methods.
Keep in Mind While Converting
- In this dataset, I converted the number with scientific notation manually. But the input number will be automatically stored with a scientific notation if the number is very large or very small. For example, the maximum limit of precision is 15 in the case of Number format. Contrarily, it is 12 in the case of General format.
- Also, using the above methods you may add leading zeros before the output which is in the Text format.
Conclusion
That’s the end of today’s session. I strongly believe you may combine cells into one with line break easily using the above methods. Anyway, if you have any queries or recommendations, please share them in the comments section below.
