View Single Post
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Rick Rothstein Rick Rothstein is offline
external usenet poster
 
Posts: 5,934
Default Split astring using Capital letter as identifier

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


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.

--
Rick (MVP - Excel)