Extract variable length information from a string in various locations

[ziechael]

Hi, I've scoured the net for hours and tried various methods but i'm no expert, just a reasonable reverse engineer.

Using excel 2003/2010 is it possible to extract an unknown length string from a larger set of information in a cell?

I have a custom formula that pulls information from an xml source in a format similar to the below:

Please Login or Register  to view this content.

I need to extract the company name from the string (which can be much longer than the above) and

Please Login or Register  to view this content.

works fine, but sometimes the string isn't as clear cut as that and may look like

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

I need an expert, is it even possible to achieve what I want?

For reference the custom formula i have is:

Please Login or Register  to view this content.

and the 'IndividualCredit' node is made up of 3 child nodes, the role type (Composer, actor, production company etc), forename (Anon, Mist etc) and surname (Eri, Emous, Company 1 etc).

Sorry if i've missed anything or broken some forum rules, just a desperate man looking for help!

[ziechael]

As a long way around I'm using 'text to columns' followed by a hlookup followed by a trim/left combo but it would be nice to have a single, simple formula to use if anyone has any insight!

[JeteMc]

This formula assumes that the text to be parsed is in B22:

Please Login or Register  to view this content.

Let me know if you have any questions.

[ziechael]

That is inspired, thanks. I wouldn't have thought to combine things in that way even if i'd spent the next week doing searches and trying things!!!

Much obliged!

[JeteMc]

You're Welcome, thank you for the feedback and for marking the thread 'Solved'. I hope that you have a good day.