Replace leading zeros

**brandnewday10** · 08-31-2010, 05:09 AM

Hi,

I am exporting company data to a IIF file and editing the data through Excel. The problem I'm having is when I export the data that contains credit card information it drops all the leading zeros from all the Expiry Dates, Start dates and CCV numbers.

I have tried using the basic formula to convert to text ( =text(A1,"0000") ) which works in Excel but when I save the IIF file it doesn't store the new data as text and reverts it back to numerical data, dropping the leading zeros again.

Is there a way to add the leading zeros in a numerical cell?

**xenixman** · 08-31-2010, 11:26 AM

If you can, open the IIF file (sorry I have no knowledge of what type of file this is) and see how the numbers are being stored there.

To retain the leading zeros in Excel, the number must be stored as TEXT,
make a new column and store the numbers with leading zeros as TEXT and use that column for exporting.
You just can't use it in calculations.

See this thread, that might help.
http://www.excelforum.com/excel-gene...ml#post2371519

**brandnewday10** · 09-01-2010, 06:34 AM

Hi,

Thanks for the reply but I don't think my problem can be solved. I have formatted several different ways but the problem is when I save the file as the .IIF it loses all formatting.

**xenixman** · 09-01-2010, 08:32 AM

when I save the file as the .IIF it loses all formatting.
Reply With Quote

Sorry, do not know what an .IIF file is or how it stores data.

**brandnewday10** · 09-01-2010, 09:25 AM

Its a file type used by QuickBooks. I think its only used when exporting data.

Its the same message that pops up when you try to save an Excel file as a .csv. Something along the lines of "when you save as this file type you may lose format settings"...roughly

**xenixman** · 09-01-2010, 09:55 AM

After you export the data from QuickBooks, try opening the .IIF file with Notepad, see how the data is stored, and post an example.

If it is a tab separated file or some combination of tabs and quote marks then when you import it to Excel, it will strip the zeros, as you have found out the hard way.

Check in QuickBooks and see if there isn't some way to make the file more Excel compatible.

If needed there is probably a way to create a filtering script that can add a tick to the beginning of all number fields in the .IIF file.

**xenixman** · 09-01-2010, 10:14 AM

Found this page; see if it is of any help:

http://support.quickbooks.intuit.com...rticle/1007545

I believe the key may be using the Import Wizard and specifying the number fields as TEXT.