Concatenate 4 rows, keeping carriage returns, and cleaning up

noppojp

Board Regular
Joined
Nov 4, 2005
Messages
69
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:

<Start>
~~

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
~~
<End>

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
 
Last edited:

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

noppojp

Board Regular
Joined
Nov 4, 2005
Messages
69
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:

<Start>
~~

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
~~
<End>

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



i have a correction to make.
The 2nd concatenation should go in Column C, next to the 1st concatenated values that were put in Column B.
So, Column B would have the Code and the Question, and Column C would have the concatenated answers A through D.

Thank you so much.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,680
Office Version
  1. 2010
Platform
  1. Windows
I am about to go to sleep, so I won't be able to follow up on this with you for a few hours, but the best I can tell, this macro seems to do what you asked for...

Code:
Sub NumberBlanksConcatenateTwice()
  Dim Index As Long, LastRow As Long, Blanks As Range, Cell As Range
  On Error GoTo NoBlanks
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Set Blanks = Range("A1:A" & LastRow).SpecialCells(xlBlanks)
  For Each Cell In Blanks
    Index = Index + 1
    Cell.Value = Index
    Cell.Offset(, 1) = Cell.Offset(1).Value & "@@" & Cell.Offset(2).Value
    Cell.Offset(, 2).WrapText = True
    Cell.Offset(, 2) = Join(Application.Transpose(Cell.Offset(3).Resize(4).Value), vbLf)
    Cell.Offset(, 2).EntireRow.AutoFit
    Cell.Offset(3).Resize(4).EntireRow.Delete
  Next
  Columns("A").Replace "~~~~", "End", xlWhole
NoBlanks:
End Sub

Note: Make sure to widen Column C enough so that the longest single line will fit without wrapping.
 

noppojp

Board Regular
Joined
Nov 4, 2005
Messages
69
Wow, that was great! It got me 95% the way there. Thanks so much. There were some minor hickups in the data, lines that i did not notice, but you nailed a very large majority of the data with your code. i'm in study mode now. Thanks a ton!

Noppo
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,680
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Wow, that was great! It got me 95% the way there. Thanks so much. There were some minor hickups in the data, lines that i did not notice, but you nailed a very large majority of the data with your code. i'm in study mode now. Thanks a ton!
You are quite welcome, I am glad I was able to help.

Just out of curiosity, what were the "hiccups in the data", perhaps I can code around them for you?
 

noppojp

Board Regular
Joined
Nov 4, 2005
Messages
69
You are quite welcome, I am glad I was able to help.

Just out of curiosity, what were the "hiccups in the data", perhaps I can code around them for you?

Hey Rick! The hiccups in the data were as follows (below). They were perfect as data goes, but i simply did not notice them as i went through 3500+ lines myself. See the bold. You will see that the question numbering changes. It's simply an explanation of what's to follow below it, e.g., what the questions below that statement are about. The question numbering changes should have been obvious to me, but they weren't at first pass.

<START>
T1A14 (A) [97.303(d)]
What must you do if ..........?
A. Stop .....
B. Nothing, because this ....
C. Establish contact with ....
D. Change to .....
~~

T1B - Authorized frequencies: frequency allocations; ITU regions; emission modes; restricted sub-bands; spectrum sharing; transmissions near band edges

T1B01 (B)
What is the ITU?
A. An agency of the ...
B. A United Nations ...
C. An independent ....
D. A department of ....
~~
<END>

i'm up and running now, but i can use the fix on a different bank of questions. This bank is the 1st of 2.

Kind Regards,
Noppo
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,680
Office Version
  1. 2010
Platform
  1. Windows
What should happen with that line... can it be deleted (by my code) or does it need to remain? If it has to remain, which blank gets the number the one above it or below it?
 

noppojp

Board Regular
Joined
Nov 4, 2005
Messages
69
What should happen with that line... can it be deleted (by my code) or does it need to remain? If it has to remain, which blank gets the number the one above it or below it?

Thanks Rick! That line can be in the first concatenate. So, it can be added below the question, but with a space between it and the question. It seems like such a waste to ignore it, especially since being below the question would not bother anything.

My bad Rick. i should have stated what needs to be done with it.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,481
Messages
5,596,393
Members
414,063
Latest member
N_Bates

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top