Excel rows to column formatting

Question

Hi,

I would really appreciate some Ninja excel help. I have a file that looks like this:
SchoolA
0
0
1
2

SchoolB
1
2
3
4
SchoolC
0
2
2
2

I want to convert this into a .csv friendly file, like this:
SchoolA,0,0,1,2

SchoolB,1,2,3,4

SchoolC,0,2,2,2

Or, make columns off of the file, like this:
SchoolA 0 0 1 2

SchoolB 1 2 3 4

SchoolC 0 2 2 2

Any help would be appreciated.

Thanks.

Answer 1

The data you showed had an extra line after the data for SchoolA but not the others. This procedure includes a preliminary steps to deal with that also.

1. Open the text file with your editor of choice. I used Notepad++. If you use a different one, you may need to find the equivalent commands.
2. Replace every occurrence of "\r\n\r\n" with "\r\n". This will remove the blank lines.
3. Replace every occurrence of "\r\nS" with "\tS". This will remove the line break after the last line in each group. If there are groups that start with a letter other than "S", repeat this for those letters also.
4. Replace every occurrence of "\r\n" with ",". This will remove the remaining line breaks from the file.
5. Replace every occurrence of "\t" with "\r\n". This will restore the line breaks after the last line in each group.
6. Rename the file from ".txt" to ".csv".
7. Open the file in Excel.