Excel: Concatenate 4 rows, keeping carriage returns, and cleaning up

Hiya, (It's actually concatenating twice, not just 4 rows... sorry, i couldn't go back and edit the title!) i have this data in Column A is 8 rows of data that does not change structure: ~~ T1A02 (C) [97.1] Which agency regulates and enforces the rules for the Amateur Radio Service in the United States? A. FEMA B. The ITU C. The FCC D. Homeland Security ~~ It goes on for 3500 lines, and what needs doing in Column B is: * of course, ignoring the "Start" and "End", as they are not part of the data........ * Put a 1 on the first line (a blank in the data), which is the 1st line after the double tildes * Join (concat the 2nd line starting with T****, add a "@@", and then the 3rd line which is a wrapped sentence * Concat lines 4 thru 7 (items A thru D), keeping the carriage returns * Put a "End" where the "~~" is. (i'm not sure if this needs to stay to enable further processing, but i don't need the "~~" * Go back and delete the original data in rows 4,5,6,7 or put a DD in column B for those values in column A (that way i can go back and delete them myself using a filter. Note that all of the above happens column B, where the data is in column A i'm not the greatest at VB, but can read a little bit and troubleshoot a bit, but the above is too much for my noggin. If an Excel god could push me in the right direction by showing me some VBA or a formula, either one, i'd be grateful and downright happy! For what it's worth, i will use the data by importing it into a flash card program for studying purposes. Any miracle workers out there? Thank you from Tokyo! Noppojp

This question generated 8 answers. To proceed to the answers, click here.

This thread is current as of June 13, 2015.

For more resources for Microsoft Excel