Md. Abdul Kader
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.
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.
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.
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.
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.
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
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
- 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
- How to Change Comma to Dot in Excel
- How to Convert European Number Format to US in Excel
- How to Change Decimals to Percentages in Excel
- How to Convert Percentage to Decimal in Excel
- Excel Number Format Not Working
- [Solved:] Long Numbers Are Displayed Incorrectly in Excel
- How to Display Long Numbers in Excel
- How to Enter 16 Digit Number in Excel
- How to Enter 20 Digit Number in Excel
- How to Convert Exponential Value to Exact Number in Excel
- How to Format Number with VBA in Excel
