Concatenate AND more... I think :-)

[kirban]

Hi All,

This forum has been helpful in the past so here we go again.

I am trying to extract SOME data from one cell and split it up based on a rule.

So I have this:

Team Members
Business Lead: Smith, John: john.smith@example.com
Finance Lead: Jones, Jack: jack.jones@example.com
Project Manager: Smith, John: john.smith@example.com

But I want this (Bold being heading, two columns of data):

Finance Lead Business Lead
Jack Jones John Smith

I have attached an example excel of what I am looking for and there are two sheets.

ANY HELP in the right direction would be fantastic.

Cheers

Kirban

[Paul]

It's not pretty, but all in one formula extracting the name and then reversing it...

For Finance Lead:

Please Login or Register  to view this content.

For Business Lead:

Please Login or Register  to view this content.

If you used helper columns the formulas could be much shorter. A macro could also pull the data by searching or using regex (which I'm terrible at). I'm sure even these formulas could be shortened a bit, since it's just referencing the same extracted string multiple times in order to find the start and end points and then reverse the names, but I don't have time at the moment.

Hope it helps!

[duanzhuanming]

Salut Kirban,
you can combine two functions Find and Mid:

Please Login or Register  to view this content.

see attached file for reference:
P/S: ind is a name