Ron,

Thanks very much, this code (whatever it's doing!) works beautifully. I
amended the MaxNumTitles to 14 and added a few titles to the list, but other
than that it produces exactly what I was looking for (any surprise is because
you were able to follow my convoluted question, not the fact that your code
works!). My co-worker tells me that although there are now multiple banks
listed after each title, that we can split that data apart...we'll see :)

Thank you again!
Chris

"Ron Rosenfeld" wrote:

On Mon, 12 Oct 2009 00:14:01 -0700, Calgarychris
wrote:



"Calgarychris" wrote in message
...
Hi there,

I'm working in a spreadsheet that has a column of data that I need to
split
apart. I was going to use text to columns, but unfortunately, the column
is
missing delimiting characters that would allow me to split the data
nicely.
Using find and replace to add in delimiting characters doesn't work as it
comes up with the error "formula too long". I tried to create a macro
that
would add in the delimiting characters, but the results don't appear to be
consistent (although there is no error message). An example of the column
is
he

Lead Role: Bank of ABC(33.33%), DEF Bank(33.33%), Bank of GHI(33.33%)
Coordinator: Bank of GHI(33.33) Security agent: Bank of QRS(33.33)
Facility
agent: TUV Bank(33.33)

The number of banks in each category is variable, as is the number of
categories (i.e. "titles") listed. I had thought if I could split the
data
so that columns R-?? were lead banks (there would be lots of blanks) and
then
columns XX-YY were Coordinator banks etc then I could manipulate the data
as
required. There are probably 10-12 "titles"

As I also need to extract the percentage being used in each case, I was
thinking if the macro looped through each row putting that one column into
an
array, checking for the various "titles" I needed and outputing the
results
onto a seperate sheet, that would work best but I'm terribly unsure of
array
code...

Does anyone have any idea of how I might tackle this problem?

Thanks very much!

Chris




That can be done, although it does not seem to be an efficient way to parse the
data.

But, for the sake of your question, here is one way to split the original text
as you wish, and place the title and list of banks in each adjacent cell.

The routine depends CRITICALLY on the spelling of the various titles being the
same (as you wrote) and also on your having a *LIST* of what they are. (There
are ways to parse out the names without a list, provided they are always
preceded by something like (nn.nn%), but I will leave that for now; as I
suspect there will be changes in your specifications and requirements as we go
along.

In any event, the below macro will take your sample data and parse it out as
you have specified:

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), first select the range of cells you wish to have split
up into adjacent columns. Then <alt-F8 opens the macro dialog box. Select the
macro by name, and <RUN.

Make sure the line that starts aTitles = is all on one line. Most newsreaders
will split it over two lines.

====================================
Option Explicit
Sub BankInfo()
Dim c As Range, rg As Range
Const MaxNumTitles As Long = 12
Dim aTitles() As Variant
Dim SplitArray() As Variant
Dim aBanks As Variant
Dim re As Object, mc As Object, m As Object
Dim i As Long, j As Long, p As Long
Dim s As String

'note the line below needs to be all on one line
aTitles = VBA.Array("Lead Role", "Coordinator", "Bookrunner", "Facility
agent", "Lead Bank", "Manager", "Senior Manager", "Security Agent", "etc")

Set re = CreateObject("vbscript.regexp")
With re
.Global = True
.IgnoreCase = True
.Pattern = "\b(" & Join(aTitles, "|") & "):"
End With

Set rg = Selection 'or whatever
For Each c In rg
With c
Range(.Offset(0, 1), .Offset(0, MaxNumTitles)).Clear
s = .Value
Set mc = re.Execute(s)
ReDim SplitArray(0 To mc.Count)
i = 0: p = 0
For Each m In mc
SplitArray(i) = Mid(s, p + 1, m.FirstIndex - p)
p = m.FirstIndex + m.Length
i = i + 1
Next m
SplitArray(i) = Mid(s, p + 1)
For i = 1 To mc.Count
With .Offset(0, i)
.Value = mc(i - 1) & " " & SplitArray(i)
.Columns.AutoFit
End With
Next i
End With
Next c
Set re = Nothing
End Sub
=============================
--ron