The pattern from your first sample seems to be that between banks you have a
close parens and a comma ")," and inbetween banks and roles, you just have
close parens but no comma ") ", so that would be the key delimiter.

Also, Do you really want these in sequential columns, or would there be a
benefit to having all of the information for a particular job title in the
same column, e.g.:

A B C D
Source Bookrunner Lead Bank Manager
Etc
(long string) B1(x%),B2(x%) B1(x%),B4(x%)
(long string) B6(x%),B7(x%) B6(x%),B8(x%) B7(x%)

If you needed (or would benefit from) these arranged by job title, the
approach might be a little different.


"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