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



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

Ron, You are absolutely correct. I read it wrong. So, still keeping it simple, how about this.

Sub iftwocaplettersbringuprow()
'assumes column A Correct wordwrap if necessary
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, 1) = Cells(i - 1, 1) & " " & Cells(i, 1)
Rows(i).Delete
End If
Next i
Columns(1).AutoFit
End Sub