Excel question

Heir

I have a text file.

It is filled with thousands of rows of data that follows this format:

last name, first name, email, passkey, etc, etc

Each category is separated by a comma.

I need to separate each category into a different column when I open it in excel.

Is there a way to do so?

I apologize, I'm terrible with Excel. Thanks in advance.

Oh, and also...each line is enclosed in ""

rfalias

Heir wrote: »

I have a text file.

It is filled with thousands of rows of data that follows this format:

last name, first name, email, passkey, etc, etc

Each category is separated by a comma.

I need to separate each category into a different column when I open it in excel.

Is there a way to do so?

I apologize, I'm terrible with Excel. Thanks in advance.

Oh, and also...each line is enclosed in ""

Open it as a .csv, or just change the extension to .csv, should work fine.

Heir

Didn't seem to do it.

rfalias

Heir wrote: »

Didn't seem to do it.

What does it look like in excel when it opens up?

midgetspy

You could also use any decent text editor to search/replace "," for " " or "\t" (tab) and then just paste it in.

rfalias

Actually, just go into excel, do file->open.
Change the file type to 'Text File'
Find that .txt and open it.
On the next dialog, click the delimited radio button, then click the comma separator.

PracticalProblemSolver

so it's like:

"john doe, blah blah, xlkjsdfjlsd, etc etc"

???

You probably just need to delete the quotes.

Deebaser

Heir wrote: »

Oh, and also...each line is enclosed in ""

Practical is right, this is what is fucking you up. Find/replace quotemarks with nothing and you should be able to open it up just fine.

How the hell did they get it to export like that anyway?

Heir

It's just a weird program we have...it wrapped each line in quotes.

I ended up just writing a batch file that scrubbed out the quotes for me. Then used excel to import it looking for commas as the delimiters.

Thanks

rfalias

Shouldn't have even needed a batch, notepad and Ctrl+h and replace quotes with nothing.
That aside; excel 2003 does differently than 2k. 2k3 treats quotes as a whole string thus puts it all into one column, but 2000 lets you choose the delimiter and ignores quotes.

Peculiar.

Sir Carcass

There's also Data -> Text to Columns

Corvus

Sir Carcass wrote: »

There's also Data -> Text to Columns

This is what you need. Just select your data, fire up Text to Columns, tell it where you want to split it (on the commas) and it will separate it all into columns for you.