How to store numbers as text in Excel
A free Office suite fully compatible with Microsoft Office
Any information you enter, such a number, will normally be kept as a number. The number can be used and changed later on for analysis. But in some special situations, we must keep numbers as texts in order to perform part-by-part data searches, match two cells, improve visualization, or for any other purpose.
Excel Number Stored as Text
We have a dataset that contains the name of the employee along with their employee ID number. The ID must be saved as text in the following column.
Let's start now.
A number can be easily stored as text by using Text Format. Your entered number will be treated as text by Excel. Utilize the format as directed by the procedures.
1. Choose the cell range that you want to modify, for example, D5:D11.
2. From the Home tab, select the Number command from the drop-down list.
3. Select the Text menu item.
You can now record the number as text in your cell range. Let's add your info now.
A small green triangle with a yellow sign on one side may be seen in the cell's upper left corner.
You will see the following choices if you click on the symbol. The first one, Number Stored as Text, indicates that your input is now text and no longer a number. Well, you may get rid of the triangle by just choosing Ignore Error. Prior to selecting the choice, be certain to choose a cell or cell range.
You'll then see the result below with the number stored as text. Given that leading zeros cannot precede a number without being treated as text.
Custom Format is a significant type of format available in Format Cells, where you can choose from a wide range of settings.
1. Decide which cell range to store a number as text in.
2. Tap the Number command's lower-right corner.
3. Select Custom from the Format Cells menu.
4. As I mentioned, the Employee ID has six digits, so enter 000000.
5. Click OK
If you use the Custom Format in a cell or cell range, you don't have to enter leading zeros.
Adding Apostrophe (‘)
The simplest approach to store a number as a text is to add an apostrophe. The biggest benefit is that you can edit the cell and the apostrophe is available in the formula bar but not displayed in the cell.
1. You must type '489980 rather than just 489980 to use the procedure.
2. You will notice a tiny green triangle and several options, such as error checking options, when you enter in this manner. Click the choice.
3. Press OK after deselecting or removing the check mark from the numbers that are formatted as text or are preceded by an apostrophe.
4. By applying in this way, you may quickly remove the warning and all input will now be treated as text.
Did you learn how to use Excel to store numbers as text? To discover more about the functionality of Word documents, Excel spreadsheets, and PowerPoint slides, follow WPS Academy. To edit word, excel, and PowerPoint documents for free, you may also download WPS Office. Get it now! and have a pleasant and straightforward working experience
- 1. How to get month name from a date in Excel (3 easy ways)
- 2. Check if value is in list in Excel (3 easy methods)
- 3. How to Copy File Names in Excel from a Folder?
- 4. How to add text to beginning or end of all cells in Excel
- 5. How to color cell based on value in Excel?
- 6. How to compare two excel sheets and highlight differences