(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?
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!
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