How to stop Excel from formating the 16-digit account number in a way to avoid making the last digit zero? [duplicate]
I have large numbers of bank account numbers of different banks. Some bank account numbers start with zero and some have 16 digits. When I paste the data in Excel the leading zeros will be removed and the last digit of 16-digit account number will be zero.
How can I avoid this problem? I tried to put a special character before every account number to make it as text and paste to Excel but when I remove the special character the problem appears again.
Solution 1:
Before pasting, right-click the column which will contain the account numbers, and choose "Format Cells". There, select "Text". This will stop excel from trying to interpret the data you're pasting as numbers, and rounding them off because they're too big.
Solution 2:
If you don't need to use the numbers, use Text Import Wizard, and after tell to consider the numbers text.
Read the 3rd step carefully.
Column data format Click the data format of the column that is selected in the Data preview section. If you do not want to import the selected column, click Do not import column (skip).
After you select a data format option for the selected column, the column heading under Data preview displays the format. If you select Date, select a date format in the Date box.
Choose the data format that closely matches the preview data so that Excel can convert the imported data correctly. For example:
- To convert a column of all currency number characters to the Excel Currency format, select General.
- To convert a column of all number characters to the Excel Text format, select Text.
- To convert a column of all date characters, each date in the order of year, month, and day, to the Excel Date format, select Date, and then select the date type of YMD in the Date box.
Solution 3:
Excel uses double precision floating-point format which can only hold ~15 digits of precision. You must store the value as text while pasting with "paste special" or format the cell as text before pasting like others said. If you're typing by hand then put an apostrophe ' before the string
http://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel