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:

=MID(LEFT(MID(Data!B2,FIND("Finance Lead: ",Data!B2)+14,FIND("Project Manager:",Data!B2)-1),FIND(":",MID(Data!B2,FIND("Finance Lead: ",Data!B2)+14,FIND("Project Manager:",Data!B2)-1))-1),FIND(",",LEFT(MID(Data!B2,FIND("Finance Lead: ",Data!B2)+14,FIND("Project Manager:",Data!B2)-1),FIND(":",MID(Data!B2,FIND("Finance Lead: ",Data!B2)+14,FIND("Project Manager:",Data!B2)-1))-1))+2,255)&" "&LEFT(LEFT(MID(Data!B2,FIND("Finance Lead: ",Data!B2)+14,FIND("Project Manager:",Data!B2)-1),FIND(":",MID(Data!B2,FIND("Finance Lead: ",Data!B2)+14,FIND("Project Manager:",Data!B2)-1))-1),FIND(",",LEFT(MID(Data!B2,FIND("Finance Lead: ",Data!B2)+14,FIND("Project Manager:",Data!B2)-1),FIND(":",MID(Data!B2,FIND("Finance Lead: ",Data!B2)+14,FIND("Project Manager:",Data!B2)-1))-1))-1)

For Business Lead:

=MID(LEFT(MID(Data!B2,FIND("Business Lead: ",Data!B2)+15,LEN(Data!B2)),FIND(":",MID(Data!B2,FIND("Business Lead: ",Data!B2)+15,LEN(Data!B2)))-1),FIND(",",LEFT(MID(Data!B2,FIND("Business Lead: ",Data!B2)+15,LEN(Data!B2)),FIND(":",MID(Data!B2,FIND("Business Lead: ",Data!B2)+15,LEN(Data!B2)))-1))+2,LEN(Data!B2))&" "&LEFT(LEFT(MID(Data!B2,FIND("Business Lead: ",Data!B2)+15,LEN(Data!B2)),FIND(":",MID(Data!B2,FIND("Business Lead: ",Data!B2)+15,LEN(Data!B2)))-1),FIND(",",LEFT(MID(Data!B2,FIND("Business Lead: ",Data!B2)+15,LEN(Data!B2)),FIND(":",MID(Data!B2,FIND("Business Lead: ",Data!B2)+15,LEN(Data!B2)))-1))-1)

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:

=SUBSTITUTE(MID(Data!$B$2,FIND(":",Data!$B$2,ind)+1,-FIND(":",Data!$B$2,ind)+FIND("@",Data!$B$2,ind)-1),"."," ")

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