Thread: Leading Zero's Problem

I got some awesome help the first time I asked for it on a previous problem so thought I would try again.. Any help you can give me will be much appreciated.

I have 11000 cells with 1 digits , 2 digits , 3 digits, 4 digits, 5 digits, 6 digits and 7 digit number.

I am looking for a formula or macro to add the following:

6 zero's before 1 digit,
5 zero's before 2 digit
4 zero's before 3 digits
3 zero's before 4 digits
2 zero's before 5 digits
1 zero before 6 digits

Every cell in the W/O column needs to have 7 digits with 0's at the beginning of the numbers if the number is less than zero. I will then create a csv file so I can import it into the database. It is a requirement that each number have 7 digits so this is the only way I can think of doing it.

I attached a sample file but if the macro works on this I am sure I can run it against a file with 11,000 cells in the W/O Number Column.

Thanks in advanced for help..

Hello,

Insert a new (helper) column and use this formula:

=TEXT(A2,"0000000")

Copy down.

Copy the helper column, then use Paste Special > Values to paste the values into column A.

Save as CSV.

cheers,

Hi bzenker,

I think she suggested you save the column that had all those leading zeros over the top of your original values. If you did this, then you would have text in the original coloumns and wouldn't need any formats.

The other answer is that if you save as CSV, formats are not saved.

CSV files are text files, and differ from txt files only in that the csv extension serves to indicates that data values are delimited by commas. Text files don't store formatting.

When you open a csv file, excel recognized that these are numbers rather than text and converts them back a number. One option would be to save them as tab-delimited text file (*.txt) and when you open the file use the data import wizard to designate the import as text rather than the default (which is general).

Hope this helps.

abousetta