If you do want to put each job into it's own column, here are a few formulas
to help you get started. Assumes your data starts in A2 (e.g. A2:A8000)
(1) Put the job titles (e.g., "Lead Role:", etc) in the column headers (B1,
C1, etc)
(2) Find the location that role occurs in your source string (put in cell B2
for now)
=FIND(B1,$A2)
(3) Find the location of the next break (put in cell B3 for now)
=FIND(") ",$A2,B2)
(4) put those strings together (put in cell B4 for now)
=MID($A2,B2,B3)
(5) To save space, combine all of this into one formula in B2:
=MID($A2,FIND(B1,$A2),FIND(") ",$A2,FIND(B1,$A2)))
now you can clear out the other cells we used above- those were just to show
you how it all works. You should be able to autofill this formula across your
entire worksheet.

Again, this puts each role in it's own column, so you would have blanks in
each row when a role isn't in the source data.

HTH,
Keith



"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