Excel Find Help

[Martynt74]

I currently run a report which returns a text string seperated by multiple characters. I'm trying to automate splitting it all out rather than using a combination of text to columns, find/replace and manual changes to get the data into a useable format.

An example of the text string is here

Martyn Thompson TEST (11483) £900.0. Awaiting:['Ryan Giggs', 'Eric Cantona', 'Paul Scholes']

Where the first element is the name of the user - Martyn Thompson
The Second element is the name of the expense report - TEST
The Third element is the unique reference in brackets - (11483)
The Forth element is the amount - £900.00
Then the end it says Awaiting and then the individuals who can authorise the claim are held within apostrophes. In this case there are 3, Ryan Giggs, Eric Cantona and Paul Scholes (Not Real employees!)

I've got the following which pulls out the first name (assuming it's not a double barreled surname)

=TRIM(LEFT(A2,FIND(" ",A2,FIND(" ",A2,1)+1)))

Then pulling out the amount is pretty easy.

What i'm struggling with is pulling out the name of the authorisers. There are probably a maximum of 5 people who can be shown within this section all identified by 's and separate by a comma. Like I said the easy option would be to just use text to columns splitting the field whenever there is a comma and then removing the apostrophes with a find/replace. But other than using a mind boggling amount of Nested Find functions i'm at a bit of a loss to do this using formulae.

As an example i ended up trying to use this for the nested IF for the first authoriser but it only works when there are 3 or more!

=MID(A17,(FIND("['",A17,1)+2),(FIND("', '",A17,1)-((FIND("'",A17,1)+1))))

Any ideas?

 

[andshrew]

This isn't particularly easy, I assume you can't change you report to add proper delimiters between each field?

For your authorisers the best I can suggest would put all the names comma seperated into a single cell; so if you wanted them in their own cell you would still need to use the text-to-columns tool, and this is completely dependant on your string being in this format.

Assuming your string is in A1:
=MID(A1,FIND(":[",A1,1)+2,FIND("]",A1,FIND(":[",A1,1)+2)-FIND(":[",A1,1)-2)

Would return: 'Ryan Giggs', 'Eric Cantona', 'Paul Scholes'

Or
=SUBSTITUTE(MID(A1,FIND(":[",A1,1)+2,FIND("]",A1,FIND(":[",A1,1)+2)-FIND(":[",A1,1)-2),"'","")

Would return: Ryan Giggs, Eric Cantona, Paul Scholes

That should work for as many or few authorises as you like.