CODEWITHSUNDEEP
excel
wingerse
wingerse

Reputation: 3796

Converting empty rows to commas in excel

I have a text file like this.

Name

Address

Road

Name

Address

Road

I want to convert this to 

Name,Address,Road
Name,Address,Road

I don't know how to do this in excel. Can anyone help? I have tried searching Google with no luck.

Upvotes: 0

Views: 86

Answers (2)

pnuts
pnuts

Reputation: 59485

With the first Name in A2 (C1 blank), in B2: 

=MOD(ROW(),3)

in C2: 

=IF(B2=2,A2,C1&","&A2)

both copied down to suit. Then select, Copy, Paste Special, Values over the top, Filter to select and delete 0 and 2 in ColumnB, then remove filter and delete ColumnB.

In effect very similar to @user2348184's approach, though perhaps a little simpler.

NB If the first Address is in A4, filter and delete blank rows (except Row1) first.

Upvotes: 1

Jeff Anderson
Jeff Anderson

Reputation: 819

Not sure how automated you need it to be but this works:

  1. Open the text file in excel so the fields show up in column A, each in it's own cell.
  2. Number column B starting with 1, increment by 1 for each row(1-n where n is number of rows)
  3. In column C insert a formula "=MOD(B1, 3)", this will give you the remainder of column B and 3
  4. In column D, insert a formula, starting at the third row and going to the end of the rows: "=IF(C3=0,CONCATENATE(A1,",",A2,",",A3), "")", this will get you the comma separated rows you are looking for, but only in every third row, the rows in between will be blank.
  5. Copy and paste column D into a new worksheet, delete the blank rows, either in excel or in the text editor.

Upvotes: 1

Related Questions