Quote:
Originally Posted by Don Guillett[_2_]
On Friday, March 23, 2012 10:14:32 PM UTC-5, dogplayingpoker wrote:
I have text that looks like this:
###
FOUR SCORE and seven years ago
our fathers brought forth on
this continent
A NEW NATION conceived
in liberty and dedicated
to the proposition
that all men are created equal
NOW WE ARE ENGAGED in a great civil war
testing whether that nation
OR ANY NATION
so conceived and so dedicated
can long endure
WE ARE MET ON A GREAT battle-field
of that war
###
There is no regularity to the number of lower-cased rows between the
capitalized rows, nor is there any regularity to how many letters are
capitalized at the beginning of the capitalized rows. I want the output
to look like this, so that Excel combines rows, adding a space at the
front and starting a new row when it hits a capital letter:
###
FOUR SCORE and seven years ago our fathers brought forth on this
continent
A NEW NATION conceived in liberty and dedicated to the proposition that
all men are created equal
NOW WE ARE ENGAGED in a great civil war testing whether that nation
OR ANY NATION so conceived and so dedicated can long endure
WE ARE MET ON A GREAT battle-field of that war
###
And to make things even more complicated, sometimes the lower-cased rows
actually start with a single capitalized letter. So really, I want it to
start a new row when it hits more than TWO capital letters in a row.
Any ideas? Thanks in advance.
--
dogplayingpoker
Sub insertrowiftwocapletters()
Dim i As Long
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Left(Cells(i, 1), 2) = UCase(Left(Cells(i, 1), 2)) Then
Cells(i, 1) = " " & Cells(i, 1)
Rows(i).Insert
'MsgBox i
End If
Next i
End Sub
|
Thanks! This did part of what I want, but its not quite there. There is now an empty row before each string of two or more capitalized letters, but it didn't combine the rows. I'd like the output to be such that every single row starts with a string of the all caps text. Right now it shows
AAAAAAAAbbbbbbbbbccccccc
ccccccccccccccvvvvvvvvvv
BBBBBBBBccccffffffgghjjkkkk
ggggggggggggggggggggg
ddddddddddddddddd
eeeeeeeeeee
CCCCCCCCCgggggghhhhhh
ffffffffgfgfg
And I'd like it to look like:
AAAAAAAAbbbbbbbbbcccccccccccccccccccccvvvvvvvvvv
BBBBBBBBccccffffffgghjjkkkkgggggggggggggggggggggdd dddddddddddddddeeeeeeeeeee
CCCCCCCCCgggggghhhhhhffffffffgfgfg