Leading zeros dissapearing / Removal of character 2

[keepingbusy]

I have written a piece of code (Using Visual Foxpro) that exports data to a TAB file which automatically opens in Excel.

I am using Excel 2007 with Windows 7

Column B in the spread sheet is a list of 12 digit numbers for example: \123456789012

Some of the numbers (which by the way are SKU/UPC) look like:

\000342134567 or
\000002167321

The reason for the leading character \ is that we were losing leading zeros when the sheet opened.

Having looked at the many sollutions posted by Tek-Tippers I cannot resolve my issue.

When the spread sheet is open I have tried the REPLACE ALL option asking for the leading character \ to be removed and replaced with a apace or nothing but the zeros are removed.

I also tried a couple of formulas but these too, removed the leading zeros.

Any suggestions or guidance would be appreciated guys.

Thanks

 

[keepingbusy]

I forgot to mention that we tried to change the columns (B) format to TEXT and this didn't work either.

 

[SkipVought]

hi,

faq68-6659

Formatting a range changes NOTHING in the underlying data.

You must CONVERT your NUMBERS to TEXT.

This is your problem...

a TAB file which automatically opens in Excel.

Rather than OPENING the text file with Excel, open a NEW WORKBOOK and IMPORT your file using Data > Get External Data > From Text files...

The Text File Open Wizard looks much like the Text to Columns Wizard. With both of these wizards, you can specify the DATA TYPE of any export column. In your case, specify column B as TEXT, and all will be well. Check your other columns as well to determine the correct column data type.

BTW, once you set up this Text File QueryTable on your sheet, you only need to REFRESH the QueryTable, if you need to import a file with new data.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[keepingbusy]

Skip

Thanks for the advice. In my years of using Excel I didn't realise then when you opened a TAB or TXT file that you could actually change individual columns during the wizard process.

Appreciate your time.

Lee