Convert Long Numbers to Text in Excel

When you type a number that has more than 15 digits in a cell in Excel, Excel converts any digit beyond the fifteenth position to a zero.

This can render invalid, for example, a credit card ID number that contains more than 15 digits. You can avoid this problem by converting long numbers to text. This tutorial shows two methods of how to convert long numbers to text in Excel.

Method 1: Precede the Numbers with Single Quotation Marks

This method is suitable for small datasets.

Suppose we have the following information about clients’ credit card ID numbers in a Word table:

Each client’s credit card ID number is seventeen digits long. We are required to enter the credit card ID numbers in the dataset below:

We use the following steps:

1. Select cell B2.
2. Type in an apostrophe followed by the credit card ID number of the first client.

All seventeen digits are displayed in cell B2.

Note: Look in the formula bar to see the apostrophe that precedes the number.

3. Enter the credit card ID numbers for the other two clients in cells B3 and B4 in the same way you did for the first client.

Get Rid of the Error Indicators

If you select any of the cells that contain credit card ID numbers, an error indicator appears next to it.

To get rid of the error indicator, click on it and choose Ignore Error on the shortcut menu that appears.

Get rid of the error indicators in cells B3 and B3 in the same way. The dataset looks clean without the error indicators.

Method 2: Apply The Text Format to The Cells Then Enter The Data

To avoid having to type an apostrophe in every cell before entering the numbers, you can apply the text format to the cells before entering data.

Suppose we have the following information about clients’ credit card ID numbers in a Word table:

Each client’s credit card ID number is seventeen digits long. We are required to enter the credit card ID numbers in the dataset below:

We use the steps below:

1. Select range B2:B4.

2. Open the Format Cells dialog box using any of the following ways:

Press Ctrl + 1

Or

Right-click the selected range and choose Format Cells on the shortcut menu:

Or

Select the Home tab, and click the Number Format dialog box launcher icon in the right bottom corner of the Number group:

3. In the Format Cells dialog box that appears, in the Number tab, select Text in the Category list box and click OK.

4. Enter the credit card ID numbers in cells B2, B3, and B4.

Note: To get rid of the error indicators in range B2:B4 at one go instead of doing it one at a time, do the steps below:

• Select range B2:B4.

• Click the error indicator next to cell B2 and choose Ignore Error on the shortcut menu.

All the error indicators are removed from the dataset.

Conclusion

This tutorial showed two methods of how to convert long numbers to text. In the first method, we first type in an apostrophe before entering the long numbers. In the second method, we first apply a text format to the target cells before entering the long numbers.