Bring through job title in between two * asterisks

[Declamatory]

Morning folks

I have a spreadsheet that has a column with peoples job titles in. Examples of how the data looks is as follows:

HSCB*002*IRB Board Member*Contract*
HSCC**Commissioning Manager*CS*

In another column I would like to bring through the job title that can be found after the second asterisk and before the last asterisk. So the data would look like

IRB Board Member
Commission Manager

Can anybody help me with a formula that could achieve this.

Thanks

Darren

 

[steve the fish]

See if this works:

=TRIM(MID(SUBSTITUTE(A1,"*",REPT(" ",LEN(A1))),LEN(A1)*2,LEN(A1)))

 

[RoryA]

Try this:

=TRIM(LEFT(SUBSTITUTE(MID(A2,FIND("*",A2,FIND("*",A2)+1)+1,LEN(A2)),"*",REPT(" ",1000)),1000))

 

[Declamatory]

Thanks guys, I really appreciate it.