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