On Sat, 24 Mar 2012 03:14:32 +0000, dogplayingpoker wrote:

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.


Your request can do with some details.

For example, you write that you want to start a new row only when you have more than TWO capitalized letters in a row, yet your line that starts with:

A NEW NATION conceived in li

only has one capitalized letter followed by a space.

It is also not clear where you want the results to go, or where your source is.

I have made some assumptions:

Your source is in column A, starting with row 1
Your results will go into column B, starting with row 1
You will start a new line if it starts with two consecutive capital letters or a pattern of Capital<spaceCapital

There are some other limits in the code, having to do with size, but I doubt they will cause an issue, given the information you have provided.

To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8 opens the macro dialog box. Select the macro by name, and <RUN.

============================================
Option Explicit
Sub SplitTextAtCaps()
Dim rg As Range, c As Range
Dim rDest As Range
Dim s As String
Dim v As Variant, vSrc As Variant
Dim re As Object
Set rg = Range("A1", Cells(Rows.Count, "A").End(xlUp))
Set rDest = rg(1, 1).Offset(columnoffset:=1)
vSrc = rg
For Each v In vSrc
s = s & vbLf & v
Next v
s = Mid(s, 2)

Set re = CreateObject("vbscript.regexp")
With re
.Global = True
.Pattern = vbLf & "(?![A-Z]\s?[A-Z])"
End With

s = re.Replace(s, " ")
v = Split(s, vbLf)

rDest.EntireColumn.Clear
Set rDest = rDest.Resize(rowsize:=UBound(v) + 1)
rDest = WorksheetFunction.Transpose(v)

End Sub
==================================

Worked perfectly. Thank you very very much.

Well done Ron


Gord


On Sun, 25 Mar 2012 00:25:34 +0000, dogplayingpoker
wrote:


'Ron Rosenfeld[_2_ Wrote:
;1600164']On Sat, 24 Mar 2012 03:14:32 +0000, dogplayingpoker
wrote:
-
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.
-

Your request can do with some details.

For example, you write that you want to start a new row only when you
have more than TWO capitalized letters in a row, yet your line that
starts with:

A NEW NATION conceived in li

only has one capitalized letter followed by a space.

It is also not clear where you want the results to go, or where your
source is.

I have made some assumptions:

Your source is in column A, starting with row 1
Your results will go into column B, starting with row 1
You will start a new line if it starts with two consecutive capital
letters or a pattern of Capital<spaceCapital

There are some other limits in the code, having to do with size, but I
doubt they will cause an issue, given the information you have
provided.

To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8 opens the macro dialog box. Select the
macro by name, and <RUN.

============================================
Option Explicit
Sub SplitTextAtCaps()
Dim rg As Range, c As Range
Dim rDest As Range
Dim s As String
Dim v As Variant, vSrc As Variant
Dim re As Object
Set rg = Range("A1", Cells(Rows.Count, "A").End(xlUp))
Set rDest = rg(1, 1).Offset(columnoffset:=1)
vSrc = rg
For Each v In vSrc
s = s & vbLf & v
Next v
s = Mid(s, 2)

Set re = CreateObject("vbscript.regexp")
With re
.Global = True
.Pattern = vbLf & "(?![A-Z]\s?[A-Z])"
End With

s = re.Replace(s, " ")
v = Split(s, vbLf)

rDest.EntireColumn.Clear
Set rDest = rDest.Resize(rowsize:=UBound(v) + 1)
rDest = WorksheetFunction.Transpose(v)

End Sub
==================================
Worked perfectly. Thank you very very much.

On Sat, 24 Mar 2012 19:09:08 -0700, Gord Dibben wrote:

Well done Ron


Gord

Thanks, Gord

On Sun, 25 Mar 2012 00:25:34 +0000, dogplayingpoker wrote:

Worked perfectly. Thank you very very much.

Glad to help. Thanks for the feedback.

On Sunday, March 25, 2012 5:54:56 AM UTC-5, Ron Rosenfeld wrote:
On Sun, 25 Mar 2012 00:25:34 +0000, dogplayingpoker wrote:

Worked perfectly. Thank you very very much.

Glad to help. Thanks for the feedback.

Actually, mine worked just fine with the sample data.

On Sun, 25 Mar 2012 07:02:19 -0700 (PDT), Don Guillett wrote:

On Sunday, March 25, 2012 5:54:56 AM UTC-5, Ron Rosenfeld wrote:
On Sun, 25 Mar 2012 00:25:34 +0000, dogplayingpoker wrote:

Worked perfectly. Thank you very very much.

Glad to help. Thanks for the feedback.

Actually, mine worked just fine with the sample data.

Don,

On my system (W7x64, Excel 2007), copy/pasting the data and your macro, your routine
Inserted a new row prior to any row starting with a capitalized string of words (including prior to the first row)
Did NOT combine the other rows.

So the result I see, after running your macro, which hopefully won't be messed up by the newsreaders:

======================

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
==========================

In interpreted that what the OP wanted was also to combine the other rows, resulting in:

===============================
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
=========================================