ExcelInsider Team
There are different kinds of data values in excel. When importing from other programs, or due to an issue in data entry, the numbers might be stored as text. But to work with that data efficiently, you would need to store it as numbers. In this article, we will learn the ‘excel number stored as text fill all’ methods.
➤ Select the column where you have numbers in as text
➤ Go to the Number Format options, and open the dropdown menu
➤ Select Number.
That was easy, right? However, this method is not viable for every case. You might want to want the numbers in another column, or using this method can be slow if you have a lot of data. Don’t worry, we will show you all the other methods to fix all the numbers stored in text.
Using Number Format to Convert Text to Number
In this dataset, we have the number of page visits to a website. Unfortunately, the information was imported from the admin dashboard, and it did not format the numbers properly. We will fix that using the methods below:
This is the most straightforward method mentioned in this article. We will change the number format in excel and call it a day. Here is how to do it:
➤ Select the column where you have the text data. You can click on the column labels (A/B/C) to select the whole column at once. To uncheck the column header, press Ctrl and click the column header.
➤ In the home tab, go to the section that says Number. The dropdown menu would say Text as the numbers are stored as text.
➤ Select Number from the dropdown menu.
Fixing Number Format with Multiplication Formula
The previous method, while effective, is very slow for large databases. If your column has thousands of rows, even a decent computer might take a while to finish. Using multiplication, we are going to make the process faster. Follow the steps below:
➤ Take a helper cell. It does not matter where you put it; just use a temporary cell as a helper.
➤ Write 1 and copy that cell.
➤ Select the cells you want to convert to numbers.
➤ From the Paste menu, go to Paste Special
➤ From the new window, select Multiply from the operation section and press OK.
➤ Go to the helper cell and press Enter to cancel the copy operation. Then you can delete that cell.
Converting Text to Number Using a Helper Column
In the previous methods, we have changed the format of the source column. However, you might want to have the numbers in a different column while keeping the source column untouched. This is how you do it:
➤ Take a helper column that shares the same rows.
➤ Put this formula in the first cell that uses the same row so that you can autofill easily:
=VALUE(B2)
➤Autofill the other cells.
Frequently Asked Questions
Why is Excel storing numbers as text?
If you import data from other programs, sometimes excel cannot recognize the format and store it as text.
How do I stop Excel from changing numbers to text?
From the File menu, go to Options. From the new window, go to Proofing > AutoCorrect Options. Then, from the “AutoFormat As You Type” tab, uncheck everything and confirm.
How do you treat a number as text in Excel?
Press Ctrl+1 to open the Format Cells dialog. Then select text from the left panel and confirm.
Why does Excel think my numbers are text?
The cells are probably formatted as text before entering the values. If the cells are already formatted, excel does not autocorrect them, although a warning might be shown.
How do I stop numbers from auto formatting?
Before entering your number, add an apostrophe (‘) in the cell.
Wrapping Up
In this article, we have learned three ways to fix numbers stored as text in excel. The practice file contains all of these methods, so you can use them to get better. Leave a comment asking what tutorial you want next, and share this tutorial with your friends.
