Long numbers in Excel not displaying properly, how to solve

Excel allows up to 11 digits to be displayed. If the number entered is more than 11 digits, it is automatically expressed in scientific notation. If the number entered is more than 15 digits, all digits starting from the 16th digit are converted to 0 and cannot be recovered. So how to enter long numbers in Excel? Excel supports inputting multiple texts in one cell. You can input long number by displaying long number as text.

In addition to entering long numbers(large numbers), sometimes formulas also return long numbers. How can such a long number exceeding 15 digits be displayed completely? You need to convert numbers to text with function in this case. If long numbers are represented by scientific notation by default when making a document and want to display them all, what should I do? The same method is used to convert numbers to text. You open a text file with long numbers directly in Excel, all long numbers are also represented by scientific notation by default, and numbers exceeding 15 digits are converted to 0, so they cannot be opened directly, you need to import them in Excel.

I, How to enter long numbers in Excel and display them completely

(I) Convert numbers to text with quotation marks(')

1. Select or double-click the cell where you want to enter the long numbers, enter a quotation marks('), then enter the 15-digit long numbers, and press Enter after the input is completed, all the entered numbers are displayed, they are not automatically expressed in scientific notation, and the preceding quotation marks are automatically hidden; the operation steps are shown in Figure 1:

Figure 1

2. Here, the function of quotation marks is to convert the entered numbers into text. It is through conversion that all numbers are displayed completely.

(II) Set the Formats cell as text

1. Select the cell where you want to enter the long numbers(such as A2), press the shortcut key Ctrl + 1, open the "Format Cells" dialog box, select the "Number" tab, and then Select "Text" on the left, click "OK", set the format of A2 to "Text", enter the long numbers, press Enter, all the numbers to be entered are displayed and no subsequent digits are turned to 0; the steps of the operation process, as shown in Figure 2:

Figure 2

2. If the long numbers are ready, you can directly copy them to the cells formatted as text, so that they will not be displayed incomplete because the number is too long.

II, How to display the results returned by formulas as long numbers in Excel?

1. Double-click cell C2, enter the formula =A2*B2, and press Enter to return the result expressed in scientific notation; select C2, move the mouse to the cell fill handle in the bottom right corner of C2, and after the mouse becomes the bold black the plus sign, double-click the left button, all the returned results are expressed in scientific notation. Double-click C2, change the formula to =TEXT(A2*B2,"0"), press Enter to return a string of long numbers, return the remaining results with the method of double-clicking the cell fill handle of C2, and they all show the numbers in full; the process steps are shown in Figure 3:

Figure 3

2. Formula description:

A. The formula =A2*B2 is the product of A2 and B2. Since A2 and B2 are relative references to columns and rows, when you drag down, A2 will automatically become A3, A4, etc., and B2 will automatically become B3, B4, etc., so the product of cells below A2 and B2 can be calculated.

B. The formula =TEXT(A2*B2,"0") is used to round and convert the product of A2 and B2 into text. The second argument "0" is used to format, "0" means only integers are retained. The Text function is used to convert numbers, dates, and times into text.

III, Long numbers in excel are cancelled scientific notation(Long numbers not displaying properly in excel)

(I) The method of custom format of cell

1. Select two long numbers in the text file window, press Ctrl + C to copy, click anywhere in the Excel window to go to the window, select cell A1, and press Ctrl + V to paste the two long numbers into A1 and A2 and automatically display in scientific notation.

2. Press Ctrl + 1, open the "Format cells" dialog box, select the "Number" tab, then select "Custom" in the bottom left corner of the dialog box, enter 0 under "Type", and click "OK", then the long numbers are cancelled the scientific notation, and all numbers are displayed, but the last digit of the numbers in A2 has changed from 5 to 0; the operation steps are shown in Figure 4:

Figure 4

Tip: Although it is possible to fully display the numbers represented by scientific notation in a custom cell format, if the long numbers exceeds 15 digits, the digits after the 15th digit are all turned to 0 and cannot be recovered(because Excel has the most 15 digits are allowed), so long digits longer than 15 digits must be converted to text.

(II) Use the "Text to Columns"

1. Select A2:A4, select the "Data" tab, and click the "Text to Columns" in the "Data Tools" group to open the "Convert Text to Columns Wizard" dialog box; select "Delimited" for "Choose the file type that best describles your data", Click "Next"; go to step 2, keep the default option for "Delimiters", click "Next "; go to step 3, select "Text" for "Column data format", and click "Finish", All long numbers expressed in scientific notation are displayed; the operation process steps are shown in Figure 5:

Figure 5

2. This method is the same as the above method. If the number exceeds 15 digits, the digits after 15 digits will be fully converted to 0 and cannot be recovered.

IV, Long numbers are opened in Excel and the last few digits become 0(Long numbers not displaying properly in excel), how to solve

(I) Operate in Excel 2019

1. Open Excel, create a blank workbook, select the "Data" tab, click "From Text/CSV" in the top left corner of the window, open the "Import Data" dialog box, and navigate to the folder where the file to be imported(such as F:\upimg\Office\Excel\document), double-click the file "long numbers.csv"to open the "Load" dialog box.

2. Click the "Transform Data" in the bottom right corner of the dialog box, open the "Power Query Editor" dialog box; press "Shift", click the "Number 2" to select the two columns; select the "Transform" tab, click the "Format" in the "Text Column" group, select the "Capitalize Each Word"(or Lowercase or Uppercase) in the pop-up menu, the long numbers to be selected are displayed.

3. Click "File" in the top left of screen, Select "Close & Load", the long numbers are imported into Excel and they are displayed completely, the last few digits are converted to 0; the operation process steps are shown in Figure 6:

Figure 6

(II) Operate in Excel 2016

1. Open Excel, create a new blank workbook, select the "Data" tab, click "Text to Columns" in the top left corner of the screen, open the "Text Import Wizard" dialog box, and navigate to the folder where the file to be imported(such as G:\Excel file\document), double-click the file to be imported to open the "Convert Text to Columns Wizard".

2. Select "Delimited" for "Choose the file type that best describles your data", "Start import at row" keep the default of 1, which means import from row 1, if not import from the first row, enter the specific number; Click "Next" to go to step 2, cancel "Tab" and check "Comma" for "Delimiters", and then check "Treat Consecutive delimiters as one".

3. Click "Next" to step 3. Select "Text" for "Column data format", then the first column under "Data Preview" becomes text, then select the second column and select "Text" again, then the second column also becomes text, click "Finish", go to the "Import Data" dialog box, don't change "Where do you want to put the data?", click "OK", Then the selected file is imported into Excel and is divided into two columns, all long numbers are displayed completely and not turned to 0 after 15th digit.
4. If the data to be imported has multiple columns, you must select "Text" for "Column data format" once for each column in step 3, otherwise only one column will be converted to text. If you want to put the imported data on another worksheet, select "New worksheet" for "Where do you want to put the data?".