How does your "envisioned layout" coordinate with the text you posted
originally (you apparently changed terms)? What you need to keep in mind
when asking questions on newsgroups is that we know nothing about your
business model except what you tell us. That means you can't take for
granted that we understand the terms you use and how they relate to each
other. In your text example, you used the term "Coordinator" where I think
you are now using "Mandated Arranger"... does that mean the second term in
front of a colon is not fixed text? If that is the case, will it always be
one word or can it be one or more words? Same question for each term in
front of a colon. I'm assuming that the text does not have "hard returns"
(line feeds) between them, correct? Anything you can tell us about the
"pattern" of the text would be helpful.

--
Rick (MVP - Excel)


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

Thanks for the reply - unfortunately the brackets are only in some of the
cells and usually only for some of the "roles" or "titles". The data I
presented is one cell's worth - there's roughly 8K rows. In terms of
laying
out the data afterwards, I had envisioned:

First Column
Lead Role: First Bank (30%), Second Bank (20%) etc.

Second Column:
Mandated Arranger: Second Bank, Third Bank, Fourth Bank, etc.

Third Column:
sub-underwriter: Eighth Bank, Ninth Bank etc

The number of columns would represent the total number of roles I have
(roughly 10 - 12 I believe just by eyeballing the data). Each row may or
may
not have each role and each row would have a different number of banks in
each role...

I hope I'm clarifying this rather than confusing things even more...

Thanks again,
Chris
"OssieMac" wrote:

Hi Chris,

I am not sure if the data example represents only one cell in your data.
If
so, then you could use Text to Columns 3 times.

Ensure you have a backup of your data before attempting this.

Initially use Text to columns with right parenthesis bracket as the
delimiter. You will finish up with cells containing something like the
following but they will be across the page not one under the other:-

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

Insert an additional column after each column of percentage data.

Use Text to columns again on each of the columns but this time use the
left
bracket as the delimiter. You now have the percentage data separated into
a
separate column something like this:-

Lead Role: Bank of ABC 33.33%

and repeated across the page for other records.

Insert additional columns again but this time to the left of each of the
percentage columns.

Use Text to columns again but this time use the colon as the delimiter
and
repeat for each column still to be separated.

Select the data and using Find and Replace, replace the comma and space
with
nothing. ie Insert a comma and a space in the Find field and leave the
replace field blank.

If I have not interpreted your request properly, then please provide an
example of how you want the data after it is split. I realize that you
need
it across the page but list them one under the other so that it can be
correctly interpreted on the post.

--
Regards,

OssieMac