5

I have large number of bank account numbers of different banks. Some banks account number 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.

marked as duplicate by phuclv, Pimp Juice IT, G-Man, Mike Fitzpatrick, music2myear Nov 7 '17 at 19:19

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

9

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.

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.
2

Excel uses double precision floating-point format which can only hold 15 digits of precision. You must store the value as text by paste special or format the cell 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

1

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.

0

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 :)

0

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.

  • Welcome to Super User! This duplicates another answer and adds no new content. Please don't post an answer unless you actually have something new to contribute. – DavidPostill Jun 8 '16 at 13:35
0

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.

  • this is overkill. Format the cell as text before pasting or just prepend with an apostrophe – phuclv Nov 4 '17 at 11:14
-1

Right-click the blank cell and select Format Cells. Go to Number and change decimal to 0, select Text, and then OK.

  • 2
    Please don't use all-caps. – bwDraco May 12 '15 at 11:53
  • That doesn't work - it will still strip leading zeros. – Judith May 12 '15 at 18:26
-1

Right-click the blank cell and select Format Cells. Go to Number and change decimal to 0, select Text, and then OK.

Its working.

Not the answer you're looking for? Browse other questions tagged or ask your own question.