[Solved] Excel Number Stored As Text 

Get FREE Advanced Excel Exercises with Solutions!

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.


Number Stored As Text in Excel: 5 Possible Solutions

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

Dataset for Excel Number Stored as Text

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 a number stored as text to a 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.

Error checking for Excel Number Stored as Text

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.

Convert Text 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.


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

Change Format to Convert to number Stored as Text

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


3. Using Paste Special

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

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.

Paste Special Option to convert excel Number Stored as Text


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.

Text to Column Feature for Excel Number Stored as Text

  • 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


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.
=VALUE(C5)

Here,

  • C5 = The Text String

VALUE function for Excel Number Stored as Text

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


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.

Download Practice Workbook


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.


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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo