How to Convert Number to Text without Scientific Notation in Excel

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.

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

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)

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

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)

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

Read More: How to Convert Number to Text with Commas in Excel (3 Easy Methods)


Similar Readings:


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)

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

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

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

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

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.

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

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.

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

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.

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.


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.


Related Articles

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo