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