Microsoft Excel Question: Download Questions PDF
Maybe you can help.... I use Excel not for calculation but for maintaining large mailing lists which I usually import from text files.Simple question... how do I keep Excel from dropping the leading zero when I import a 4-digit mail code? I NEED that zero there. I understand that I can format the cells to text and THEN TYPE the number in and the zero stays... but who has time for that? Is there a way to IMPORT or OPEN a text file in excel and keep the leading zero?
Answer:
In Step 3 of 3 of the text import wizard, you have to specify that your Mail Code field is text. In the step 3 dialog box, go down to the preview panel. Scroll right until you can see the Mail Code field. Click the grey box at the top of this column which probably says "General" right now. The whole column will highlight. In the upper right corner there is a box called Column Data Format. Click Text for that particular field (and for any other fields that need the leading zeroes).
Excel will keep the leading zeroes. It will *not* keep leading spaces, so I hope you don't need those.
If you have vast amounts of data that you have already imported and you need to quickly fill will leading zeroes, you can use the formula: If mail codes are in H2, enter this in I2:
=right("0000"&H2,4)
Copy this from I2 down to I9999. Highlight column I. Hit Ctrl-C to Copy, the Edit, Paste Special, Values, OK to change the formulas to text. Then copy column I over top of H.
Excel will keep the leading zeroes. It will *not* keep leading spaces, so I hope you don't need those.
If you have vast amounts of data that you have already imported and you need to quickly fill will leading zeroes, you can use the formula: If mail codes are in H2, enter this in I2:
=right("0000"&H2,4)
Copy this from I2 down to I9999. Highlight column I. Hit Ctrl-C to Copy, the Edit, Paste Special, Values, OK to change the formulas to text. Then copy column I over top of H.
Download MS Excel Interview Questions And Answers
PDF
| Previous Question | Next Question |
| I created a worksheet in Excel 95. In Excel 97, Excel ignores my page breaks? | Can I see a list of the ColorIndex and the corresponding color? |