How do I fix a delimited file sent in Excel 2002?

[Guest]

I was emailed three files with data that I need to tabulate. The data in all
the files appear to be delimited. I have no clue as to how to format the
data into individual cells in rows and columns, PLEASE HELP!!!

 

[Anne Troy]

A sample of the data would be helpful. Meanwhile, try selecting a column
(assuming it's all in one column) and choose Data-->Text to columns.
If there's a comma between data, use a comma as the delimiter, maybe they're
fixed width? Try the different options.
************
Hope it helps!
Anne Troy
www.OfficeArticles.com
Check out the NEWsgroup stats!
Check out: www.ExcelUserConference.com

 

[Guest]

Click on FILE, OPEN and in the FILES OF TYPE drop down list, select Text files.

After selecting the file Excel will start the import wizard where you can
pick and choose what delimiter is used (although Excel is successful most of
time when figuring this out), tell Excel what row to start the import on and
if the first row contains field names, what columns to bring in and what data
type each column holds

 

[Doug Kanter]

I was emailed three files with data that I need to tabulate. The data in
all
the files appear to be delimited. I have no clue as to how to format the
data into individual cells in rows and columns, PLEASE HELP!!!

I know what delimited means, but there are various methods. It would help to
know what YOU mean by "appear to be delimited". Can you describe what you
see in a little more detail?

 

[flummi]

When you import a text file the wizard comes up with a window where you
can select "delimited" or "fixed width".
If you select "delimited" the next screen will show you a preview of
the data and will let you choose the type of delimiter. If you pick one
it will show the impact in the preview window. If what you see seems to
be correct, go further.

If you don't think there is a sensible result with delimiters, try
choosing "fixed" on the first screen.

This will bring up a screen with a preview of your data along with a
desription of how to split the rows into fields. Basically it's like
setting tabs in Word.

Hope this helps.

Hans

 

[Guest]

When I say delimited the text is all jumbled together with commas and
quotation marks with no formatting at all. There are no columns or rows.

 

[Doug Kanter]

You should follow your question daily and respond quicker. Otherwise, your
question can sort of get left in the dust when people assume you're done
here. Anyway....onward:

The best thing you could do is provide a small sample of the text. However,
I'll take a guess. Let's say it looks like this:

"Shrimp","Sautee","Marinate","Rice","Salad"

Open the file in Excel, and a dialog box should appear, called "Text Import
Wizard". Click "Delimited", then click "Next". Uncheck Tab and check Comma.
At the bottom is a preview pane. Vertical lines will appear, showing where
Excel is about to separate the data fields. Notice too, at the upper right,
a box called "Text Qualifier". By default, you'll usually see one quotation
mark " there. This should eliminate your quotation marks.

If you click Next at this point, Excel gives you the opportunity to format
each column, just as you would if you were working in an Excel sheet. For
now, ignore this and just make sure the import works to your liking. I
usually skip the formatting step and click "Finish". Since I open the same
text file weekly, it's silly to go through the formatting step. Easier to
open the text file and then paste it all into a pre-formatted Excel sheet.

If your fields come out right, but you end up with quotation marks, you can
remove them using Find/Replace. Search for ", and replace with nothing. Or,
open the text file in a text editor first, and do the same Find/Replace
there. Another issue you may run into is that sometimes, the actual data
contains commas, like this: Really Big Distributors, Inc. Excel then
separates the "Inc" from the rest of the name and puts it in its own column.
Hopefully, that won't happen. Let me know.