On Tue, 21 Jul 2009 10:01:41 -0400, "Rick Rothstein"
wrote:

Unlike the other solutions provided, the pattern you used will not split the
text at consecutive upper case letters (which might, of course, be what the
OP wants as he didn't say in his posting). I was thinking of a first name,
middle initial, last name construction like JohnQPublic or even a first
initial, middle initial, last name construction like JQPublic. Of course,
this would not be the desired treatment for a company name like
IBMCorporation though.

As is frequently the case, any solution offered must depend critically on our
interpretation of the request, given incomplete information.

For example, your solution adds an inappropriate space he

BillO'Reilly Bill O' Reilly

And if the original happens to have a space, it adds an extra:

Bill O'Reilly Bill O' Reilly

A slight change in my regex solution (both the regex and the replacement
string) can also handle the problem of JQPublic, but, of course, it doesn't
also deal properly with IBM Corporation. I'm not sure how to programmatically
differentiate between a person's name and a corporation, in this context.

========================================
Option Explicit
Function SplitOnCaps(s As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "([a-zA-Z])(?=[A-Z])"
SplitOnCaps = re.Replace(s, "$1 ")
End Function
=======================
--ron