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 need to store numbers as texts for searching the data part by part, matching two cells, better visualization, or any other reasons.
But how can we do that?
In this article, I am going to discuss the 4 smart ways to store numbers as text in Excel including real-life examples.
Download Practice Workbook
Excel Number Stored As Text
We have a dataset where Name of Employee and their Employee ID as Number are given. We have to store the ID as a text in the next column.
We have a litmus test for checking the cell whether it 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.
Let’s get started
1. Text Format
Using Text Format is a simple way to store a number as a text.
Excel will treat your input number as text.
Follow the steps for using the format.
- Select the cell range that you want to change e.g. D5:D11
- Click on the drop-down list of Number command from the Home tab
- Pick the Text option
Now your cell range is ready to store the number as text. Let’s insert your data.
You’ll see a small green triangle at the upper left corner of the cell and a yellow symbol is on the side of the triangle.
If you click on the symbol, you’ll see the following options.
The first one is Number Stored as Text that denotes your input is no longer number and it is text now.
Well, you can remove the triangle by clicking simply on the Ignore Error. Make sure you select a cell or cell range before picking the option.
After that, you’ll see the following output where the number stored as text. As leading zeros cannot exist before a number without treating as text.
2. Custom Format
Custom Format is an important type of format from Format Cells where you have numerous options to select.
- Select the cell range where you want to store number as text
- Click on the lower right corner of the Number command
- Pick the Custom option from Format Cells
- Type “000000” as I told you that the Employee ID consists of 6-digits.
- Press Ok
If you follow the above steps, you’ll get the following
Here’s a surprising thing.
You don’t need to input leading zeros if you use the Custom Format in a cell or cell range.
3. Adding Apostrophe (‘)
Adding an apostrophe is the easiest way to store a number as a text. The main advantage is that the apostrophe isn’t visible in the cell though it is available in the formula bar and you can edit the cell.
For utilizing the method, you have to type ‘489980 instead of only 489980.
When you input in such a way, you’ll see a small green triangle and some options like Error Checking Options. Click on the option.
Now deselect or remove the tick mark from the Numbers formatted as text or preceded by an apostrophe and press Ok.
Applying in this manner, you can remove the warning easily and from now all input will treat as text.
Right now, you’ll get like the following and you’ll see that apostrophe is not visible in the cell.
4. Text to Columns
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.
- Select the cell or cell range that you want to store
- Pick the Text to Columns option from the Data tab
- Choose the Fixed Width option and press Next
Now press just Next.
At last, pick the Text option and press Finish
If you follow the above steps, the cell range will store the number as text.
Let’s explore whether the cell range is storing numbers as text or not.
Insert your data.
Probably, you’ll get like the following where we see that leading zeros are available which means the cell range stores data in text format.
Another example using the method without leading zeros is the following-
The small green triangle and warning symbol denote that the D5:D11 cell range store number as text.
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.
Also, you have to be cautious about the Excel file name, file location, and extension name.
Based on your data and requirements, select the best one for you from the above ways. Thanks for being with me. If you have any thoughts, please share them below in the comments section.
- Excel 2 Decimal Places without Rounding (4 Efficient Ways)
- Excel Round to Nearest 100 (6 Quickest Ways)
- Excel round to nearest 10000 (5 Easiest Ways)
- How to Round up Decimals in Excel (4 Simple Ways)
- How to Round Off Numbers in Excel (4 Easy Ways)
- How to Round to Nearest Multiple of 5 in Excel (3 Easy Ways)
- 4 Easy Ways to Add Leading Zeros in Excel
- Using Excel to Round to Nearest 1000 (7 Easy Ways)
- How to Round Numbers in Excel Without Formula (3 smart ways)
- How to Apply Accounting Number Format in Excel! (Easy way)