[Solved] Excel Number Stored As Text 

Once you input any data for example a number, it will be stored typically as a number. Later, we can use and manipulate the number for analysis. But in some specific cases, we may have Excel numbers stored as texts for searching the data part by part, matching two cells, better visualization, or any other reasons which we need to convert to number format from text. But how can we do that? In this article, I am going to discuss the 5 smart ways of converting to number format from text format in Excel including real-life examples.

Download Practice Workbook

5 Ways to Convert to Number If Stored as Text in Excel

We have a dataset where the Name of Employee and their Employee ID as Text are given. We have to store the ID as Number.

We have a litmus test for checking whether the cell stores a number as text or not. And it is keeping the leading zeros before a number. Leading zeros are only visible when the cell treats these as texts.

In this section, you will find 5 distinct ways to convert number stored as text to number. Let’s get started.

1. With Error Checking

This method will be applicable when you see green triangles in the left-top corner of the cells showing errors. This is a common feature as when a number is stored as Text in an Excel worksheet, Excel automatically finds it unusual. So, it provides an error-checking option located at the top left corner of the cell with a small green triangle.

Follow the steps below to work with this option to implement your intention.

Steps:

• First of all, click on the small green triangle.
• Then, you will see a list of options from the dropdown. Select Convert to Number.

• After that, you’ll see the following output where the number is stored as Text is converted to a Number as leading zeros cannot exist before a number without treating it as text.

Read More: [Fixed!] Excel Not Recognizing Numbers in Cells (3 Techniques)

2. Changing Cell Format

Changing the Cell Format is a simple way to store a number as text.

Excel will treat your input number as text.

Follow the steps for using the format.

Steps:

• First, select the cell range that you want to change e.g. C5:C11
• Click on the drop-down list of Number Format command from the Home tab

• As soon as you pick the Number option from the dropdown list, your cell will change the format stored as Text to Number.

Read More: How to Custom Number Format in Excel with Multiple Conditions

3. Using Paste Special

This procedure includes a few more steps compared to the previous ones but gives an accurate result like them.

Steps:

• Copy the data range by pressing CTRL+C and then press CTRL+ALT+V to open the Paste Special dialog box.
• Here, select Values from the Paste group and Add from the Operation> click OK.

• Hence your Text will be converted.

Read More: How to Use Number Format Code in Excel (13 Ways)

Similar Readings

• How to Round to Nearest 100 in Excel (6 Quickest Ways)
• Excel 2 Decimal Places without Rounding (4 Efficient Ways)
• How to Round up Decimals in Excel (5 Simple Ways)
• Custom Number Format: Millions with One Decimal in Excel (6 Ways)
• How to Convert Number to Percentage in Excel (3 Quick Ways)

4. Applying Text to Columns Feature

You may be stunned, however, when you want to store the number as text, the Text to Columns option is good enough for that.

Look at the following steps and figure out how it works.

Steps:

• Select the cell or cell range (i.e. C5:C11) of the number stored as text that you want to convert.
• Pick the Text to Columns option from the Data Tools group under the Data.

• Choose the Delimited option and press Next.

• Again press Next on Step 2 of the Convert Text to Column Wizard.

• At last, pick the General option under Column data format and select Destination where you want to show the result.
• Press Finish.

• If you follow the above steps, the cell range will convert the stored text.

Read More: How to Change International Number Format in Excel (4 Examples)

5. Applying VALUE Function

Another key hack to convert number stored as Text to Number is the VALUE function. This function converts a given text string that stores a number to the number format. So, let’s follow the steps to implement our task.

Steps:

• First of all, select a cell and type the following formula in that cell.

Here,

• C5 = The Text String

• Now, press ENTER and the cell will convert the text into a numerical value.
• After that, drag the Fill Handle tool below to Autofill the formula for the other cells.

• Hence, all the cells will convert texts to numbers.

Read More: How to Custom Cell Format Number with Text in Excel (4 Ways)

Things to Keep in Mind

• If you wish to keep your numbers as text and find problems with copying them into another sheet, keep them in the notepad and copy them from the notepad.
• More importantly, if your number has leading zeros, be careful about storing this type of data. Because leading zeros won’t be visible until the cell treats the whole number as text. So, converting to number is important.
• Also, you have to be cautious about the Excel file name, file location, and extension name.

Conclusion

In this article, I have tried to sum up the methods to convert Excel numbers stored as text to numbers. Based on your data and requirements, select the best one for you from the above ways. Thanks for reading the article. If you have any thoughts, please share them below in the comments section.

Further Readings

• Remove Leading Zeros in Excel (8 Easy Methods)
• How to Display Long Numbers in Excel (4 Easy Ways)
• Format a Number in Thousands K and Millions M in Excel (4 Ways)
• How to Put Parentheses for Negative Numbers in Excel
• Change Number Format from Comma to Dot in Excel (5 Ways)
• How to Format Number with VBA in Excel (3 Methods)