On Jul 21, 2:20*pm, Ron Rosenfeld wrote:
On Tue, 21 Jul 2009 00:24:09 -0700 (PDT), Raj wrote:
Hi,

I am looking for a formula/function/UDF that would split
VasantRamPatil into Vasant Ram Patil ( ie split the string into three
words using the capital letter as the initial letter of each word).
The split can be either by inserting spaces inside the string itself
or splitting the string into three strings).

Thanks in advance for the Help.

Regards,
Raj

Here is a UDF that inserts spaces.

To enter this User Defined Function (UDF), <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 User Defined Function (UDF), enter a formula like

* * * * =SplitOnCaps(A1)

in some cell.

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

Thanks to All for immediately solving the problem as well as for the
learning that there are many different ways of doing things. The
knowledge will be useful for in solving future problems.

Regards,
Raj