csv data separated by comma and space

[jbell61]

Hello, I am trying to import csv text from the clipboard that is separated: fields by comma, and rows by space. Text import wizard does not appear to able to do this.

[#2 · Accepted solution]

@jbell61 

You are correct, Excel import wizard does not split a single line into multiple rows. So I see 2 possible options:

1) Use a text editor like Notepad++ to edit the CSV file and do the line breaks at the spaces. Save. Then reopen the CSV file in Excel.
2) if the file is a short file, you could import all the data into 1 cell. Then use Data / Text to Columns to split the data at the space character. That will create multiple columns where the data was split at the space. Then Copy all the data, Paste Special / Transpose will change the column data to Row data (but all data will still be in only 1 column. Finally select the column data - Data / Text to Columns again but this time delimited by comma.

[#3]

@davheili Thanks. That's funny I was sure I was able to do this in a previous version of that wizard, i.e. it had a way of handling row delimiters as well as column delimiters.

Oh, well.

[#4]

Hi @jbell61 

Maybe not an issue anymore but since you can use space as column delimiter, you can get the csv records cell by cell on one single row, row 1.

TRANSPOSE and you in a way have used space as row delimiter.

Example Input Data

Name,No,Color,Version Madelene,7,Yellow,PDA Erika,10,Yellow,PDA Christina,12,Yellow,PDA Daniella,7,Blue,PDA Elizabeth,7,Blue,PDA Elizabeth,6,Blue,365 Johanna,44,Blue,365

Opened and found at row 1 using the wizard with space as column delimiter.

A5 transposes the columns to rows

=TRANSPOSE(A1:H1)

It can, using OFFSET, be extended to automatically adjust to the imported amount of data

=TRANSPOSE(OFFSET(A1;0;0;1;COUNTA(1:1)))

Finally, use FILTERXML to split the csv data into columns by first making it look like XML code and then parse it into separate cells.

=(FILTERXML("<c><r>" & SUBSTITUTE($A5#;",";"</r><r>") & "</r></c>";"//r[1]"))