Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

4 Answers 4

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

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

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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