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.
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.
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.
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
First select all cells in spread sheet, then go to format cells and select protection. Uncheck locked. select Number, select TEXT. All pasting should be good now. Just remember to paste as text. Also, if pasting a lot of cells, I like to paste first into notepad then select and copy again and it removes all formatting from the clipboard, so you will always paste as text.
To paste data into Excel from clipboard, and keep leading zeroes, such values (cells) could be wrapped into Excel T() formula in the clipboard text. Excel pastes it as formula, but shows calculated value. So pasted =T("0011") is shown as '0011', and have a formula underneath.
Tested in Excel 2010.
Select the cells before pasting, Format Cells -> Text, then paste. It should retain the 0s. I am assuming that you won't need to do numerical manipulation on those account numbers. Edit: Duke beat me to this as I was posting it :)
Right-click the blank cell and select Format Cells. Go to Number and change decimal to 0, select Text, and then OK.
Using import wizard on the last step before importing select the column with the numbers and change this column to text, then hit finish and it will import correctly. Same method for date and time to import in your preferred format.
Right-click the blank cell and select Format Cells. Go to Number and change decimal to 0, select Text, and then OK.
Its working.
