How to Convert Number to Text without Scientific Notation in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Convert Number to Text without Scientific Notation in Excel: 7 Methods

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.

Dataset Excel Convert 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).

Excel Convert Number to Text without Scientific Notation Using the TEXT Function


2. Applying the CONCATENATE Function

Besides, you may accomplish the same task using the CONCATENATE function which combines multiple strings into a single string. So, the formula will be

=CONCATENATE(D5)

Excel Convert Number to Text without Scientific Notation Applying the CONCATENATE Function


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)

Excel Convert Number to Text without Scientific Notation Using the TRIM Function

Read More: How to Convert Number to Text and Keep Trailing Zeros in Excel


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

Excel Convert Number to Text without Scientific Notation Utilizing the UPPER Function Utilizing the UPPER Function

Read More: How to Convert Number to Text with Commas in Excel


5. Adding a Single Quote

Also, there are other methods to convert numbers 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

Adding a Single Quote

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.

Adding a Single Quote

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. To do 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).

Adding a Single Quote

If you click on the last cell of the preview (E14 cell), you’ll get the following output.

Adding a Single Quote

Read More: Convert Number to Text for VLOOKUP in Excel


6. Applying the Format Cells Option

Surely, you might have heard 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.

Applying the Format Cells Option

Then, open the Format Cells option by using the CTRL + 1 keyboard shortcut after selecting the E5:E14 cell range. Choose the Text option from the Number tab as shown in the following dialog box. Finally, press OK.

Applying the Format Cells Option

Shortly, you’ll get your desired output stored in text format.

Applying the Format Cells Option


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.

Excel Convert Number to Text without Scientific Notation Using the Text to Column Feature

Next, you’ll see a dialog box. Select Fixed Width. Click on the Next button.

Excel Convert Number to Text without Scientific Notation Using the Text to Column Feature

Again, click on the Next button.

Excel Convert Number to Text without Scientific Notation Using the Text to Column Feature

In Step 3 of 3, you must check the circle before the Text format and click on Finish.

Excel Convert Number to Text without Scientific Notation Using the Text to Column Feature

Eventually, you’ll get the following output.

Excel Convert Number to Text without Scientific Notation Using the Text to Column Feature

Read More: How to Convert Number to Text in Excel with Apostrophe


Quick Notes

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

Checking Whether a Cell Contains Text or Not

After pressing ENTER and using the Fill Handle Tool, the output will look like the following.

Checking Whether a Cell Contains Text or Not

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.

  • In this dataset, I converted the number with scientific notation manually. However, 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.

Download Practice Workbook


Conclusion

That’s the end of today’s session. I strongly believe you may combine cells into one with a line break easily using the above methods. Anyway, if you have any queries or recommendations, please share them in the comments section below.


Related Articles


<< Go Back to Excel Convert Number to Text | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdul Kader
Md. Abdul Kader

MD. ABDUL KADER is an engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SWM, GIS, RS, and SPSS. He holds a B.Sc in Urban & Regional Planning from Chittagong University of Engineering and Technology and has shifted to become a content developer. In this role, he crafts technical content centred around... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo