Hi, group -
I've got a column of (text) cells that I'd like to split into 3
columns, by case. Right now each cell looks like:
Mixed Case text ALL CAPS TEXT More Mixed Case Text
Is it possible to split this into 3 cells, Mixed Case, All Caps, then
Mixed Case again? I'm having trouble seeing it as doable? Any ideas?
Thanks very much.
You can do it using a VBA macro making use of Regular Expressions.
The following assumes that your pattern is as you've described it -- basically
with a central region consisting of all (and only) capitalized words (although
accepting digits and other non-letter characters could be included).
If the pattern is not present, the destination cells will be blank.
In the macro, I chose to put the results next to the data, but you could change
this.
You'll also need to Set rg = to the proper range. I chose A1:A10.
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 SplitByCase()
Dim rg As Range, c As Range
Dim i As Long
Dim re As Object, mc As Object
Set rg = Range("A1:A10") 'Or whatever
Set re = CreateObject("vbscript.regexp")
re.Pattern = "(.*?)\s((\b[A-Z]+\b\s*)+)\s(.*)"
For Each c In rg
Range(c.Offset(0, 1), c.Offset(0, 3)).ClearContents
If re.test(c.Value) = True Then
Set mc = re.Execute(c.Value)
c.Offset(0, 1).Value = mc(0).submatches(0)
c.Offset(0, 2).Value = mc(0).submatches(1)
c.Offset(0, 3).Value = mc(0).submatches(3)
End If
Next c
End Sub
============================
--ron
