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