"Paul Lautman" wrote:

Right then Jeff,

I took your Prod-Cat.csv file and used Data-Import External Data-Import
Data... to bring it into Excel via the Text Import Wizard.

In Step 1 of the wizard I specified that it was a Delimted file.
In Step 2 I specified that the Delimiter was a Comma and that the Text
qualifier was a ".
In Step 3 I Selected both columns (currently saying General) using
Shift-Click and selected a column data format of Text.
Then I pressed Finish and the data came in to the sheet.

I then used Peo's formula to remove the trailing commas (obviously changing
the cell references since your data was in column B starting from B2) and
used Copy + Paste Special-Values to put the processed data into B2.

I then deleted the column with the formulas.

I then saved the sheet as a web page and re-exported the data as a new CSV
[see note 1 below] file without the trailing commas. All looked fine.

I then used the Text Import Wizard once again to bring the (now clean) data
back into a new Excel sheet and it all worked fine. The 4 character
categories were preserved in cells formatted as text as expected.

Is there anything that you need to do that does not fit into the above
process?

Regards
Paul

[Note 1] I often automate the import process using a macro. However I find
it is best when automating it, to rename the file as .txt since Excel has a
nasty habit (see previous comment on Lotus 123's superiority in data
handling) of making assumptions with files ending in .csv.

I followed your instrcutions and everything worked like a charm, I saved a
file as a text file and a .csv file, just in case I forget and open the .csv
file with excel I will still have a backup in notepad that I can replace it
with. You would think that with all the people that design web sites in this
fashion that Microsoft would come up with some kind of fix to prevent excel
from making assumptions with .csv files.
In any event I am extremely grateful for youre sharing of your expertise, I
have never used a discussion board of any kind, ever, and have suffered in
silence using excel as a glorified adding machine, their help files are not
the most user friendly, but I was able to follow what you described and
everything worked fine. Thank you again!

Best Regards,

Jeff