WPS Office

Free All-in-One Office Suite with PDF Editor

correct-icon

Edit Word, Excel, and PPT for FREE.

correct-icon

Read, edit, and convert PDFs with the powerful PDF toolkit.

correct-icon

Microsoft-like interface, easy to use.

Free download

Windows • MacOS • Linux • iOS • Android

banner

How to store numbers as text in Excel

August 1, 2022
2.7K Views

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.

Text Format

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.

Steps:

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

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

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.