How to start new row when opening a CSV in Excel?

[runningguy]

Hi,

I have a CSV file that has commas to separate columns and a space to separate rows. I can only figure out how to get Excel to separate the columns, but can't figure out how to get it to separate rows. It just opens it all up in the first row. How do I get Excel to recognize that the space means start a new row?

Thanks!

[sktneer]

I think this may involve three steps....

1) Import your CSV file using Space as delimeter.
2) Copy the row and paste special --> Transpose.
3) Use text to columns using comma as delimeter.

Does this help?

[runningguy]

Thanks for the response! It's getting me closer. The problem I'm now having is that I have some cells with text that have a space in them. But those cells are within double quotations, and I select the option of the double quotes as the text qualifier, so I'm not sure what I'm doing wrong. Maybe I'm selecting something else I shouldn't be when I import it into excel?

[runningguy]

To clarify, it's starting a new row for the spaces in the text cells. I want it to ignore those spaces.Thanks!

[sktneer]

I think you will probably need a VBA code to achieve this if it is possible, I mean if you have a logic behind it like what to be put in columns and what in rows i.e. your rules to split the text into rows and columns.
If you are open to a VBA solution, better you open a new thread in the Excel Programming / VBA / Macros sub forum. Don't forget to attach a sample workbook, Sheet1 with enough sample data in it along with the desired output shown on Sheet2 to see if someone can help you.

Good luck.