WPS Office

Free All-in-One Office Suite with PDF Editor

correct-icon

Edit Word, Excel, and PPT for FREE.

correct-icon

Read, edit, and convert PDFs with the powerful PDF toolkit.

correct-icon

Microsoft-like interface, easy to use.

Free download

Windows • MacOS • Linux • iOS • Android

banner

How to convert numbers stored as text in excel

August 18, 2022
3.0K Views

This problem of how to convert numbers stored as text in excel frequently occurs when you export data to Excel from another tool, and it can completely wreck your spreadsheets.

Here are three quick methods that tell us how to convert numbers stored as text in excel:

How to use built-in help to convert numbers stored as text in excel online, 2016 and 2019:

Although this approach won't always work, when it does, it's by far the simplest.

When Excel detects that a collection of numbers you have entered as text should perhaps be stored as numbers, it will alert you. A tiny green triangle will be seen in the cell's upper-left corner:

You can immediately change all of those numbers to the appropriate format once you see this.

Let's begin with the first number column (Column A).

1.Choose every cell bearing the green triangle:

2.A little box with an exclamation point will be visible, in this case appearing over cell B2. Then click Convert to Number after that:

3.That completes the storage of all these numbers as numbers:

4.Since the numbers are now on the right side of the cells rather than the left, it is obvious that these are now stored as numbers.

How to use paste special to convert numbers stored as text in excel:

1.Another option to change the numbers' format is to multiply the list by a number; use 1 to ensure that none of the numbers are altered.This time, we'll focus on column C.Add 1 to a worksheet cell that is empty:

2.Copy the cell where you just typed 1 (Ctrl + C):

3.Choose the conversion range for the numbers:

4.To paste special, right-click over the list of numbers and choose Paste.

5.Choose Multiply, then click OK.

5

6.The column is now formatted as a number throughout. Just remove the 1 from cell D1 and you're done.

How to change the format of the column to convert numbers stored as text in excel:

1.Despite being the simplest approach, I ranked it third since it only functions with the simplest text-formatted numbers. Now let's look at column E. Every number is formatted as text, but only some cells will accept a change in format.Choose the cells that need to be changed:

2.Change the text Text to Number in the Number field on the Home tab:

3.Take a look at the figures now; this technique was only effective for the range of E7 to E11:

You are finished for those numbers. However, the numbers from E1 to E6 did not work with this strategy. This is due to the fact that those numbers either contain a space or an apostrophe in front of them; 

altering the format will not eliminate this problem. Because of this, methods 1 and 2 will produce results that are more consistently correct although requiring a bit more labour.

Note: This above written article is an attempt to show you how to convert numbers stored as text in excel online, 2016 and 2019, in both windows and mac.You just need to have a little understanding of how and which way things work and you are good to go. With having this basic knowledge or information of how to use it, you can also access and use different other options on excel or spreadsheet. Also, it is very similar to Word or Document. So, in a way, if you learn one thing, like Excel, you can automatically learn how to use Word as well because both of them are very similar in so many ways. If you want to know more about WPS Office, you can download WPS Office to access, Word, Excel, PowerPoint for free.                                    

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.