Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

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.

share|improve this answer

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.
share|improve this answer

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

share|improve this answer

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

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer
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 at 13:35

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

share|improve this answer
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .