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

Sorry Rick, obviously I'm a newb...

Each cell in this column has a variable number of titles (Lead Role,
Coordinator, Bookrunner, Facility agent, Lead Bank, Manager, Senior Manager,
etc etc etc) - they are not always listed in order, but are always spelt the
same. There are no carriage returns in the data

After each title there is a colon and then a list of banks, the names and
quantities of banks are both variable.

What I would like is to put the title and all the banks that fall into that
title into a column to the left of the original column.

So assuming the data is in Column A then column B would look like this:

<Title1: Bank1, Bank2, Bank3

Column C

<Title2: Bank1, Bank54, Bank23

Column D

<Title3: Bank12, Bank20, Bank9

I don't know how to post a sample of the data, or I would...

Thanks
Chris

"Rick Rothstein" wrote:

I think it would help us, using the example text you posted, if you showed
us how the text would look after being split apart... that is, what parts of
the text go in which columns.

--
Rick (MVP - Excel)


"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





And here is another routine which does NOT require knowing the names of the
Titles, but does require that each title is preceded by a ")" as in your
examples:

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

Set rg = Selection
Set re = CreateObject("vbscript.regexp")
With re
.Global = True
.Pattern = "\b([^):]+:)([\s\S]*?)(?=\b([^):]+:)|$)"
End With

For Each c In rg
With c
s = .Value
If re.Test(s) = True Then
Set mc = re.Execute(s)
i = 0
For Each m In mc
With .Offset(0, i + 1)
.Value = m.SubMatches(0) & " " & _
m.SubMatches(1)
.Columns.AutoFit
End With
i = i + 1
Next m
End If
End With
Next c

Set re = Nothing
End Sub
======================================

--ron