Formula required Need to separate subject line according to criteria

[chris1979]

Hi Guys
Need your assistance in separating the words given in the subject line

For eg : the subject line as below
Returned Ticket Notification INC000000515843 Returned CI: SAP: Transport Equip Missing Information

I want to separate word which comes after Returned CI till ":" or "-". In this this case ....I want to get SAP till ":" or "-"

Also I want a formula to separate the word after SAP with either ":" or "-" or " " to capture the word or words after : or - or blank

So the cell should contain - "SAP" in one and another it should be "Transport Equip Missing Information"

Regards
Chris T

 

[chris1979]

Any ideas ?

 

[Michael M]

So are you saying CI: SAP: could also be CI, SAP, or CI-SAP- or a combination of ???

 

[chris1979]

Hi Michael
It could be a space or a colon

Regards
CT

 

[chris1979]

HI - this has worked with the following

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A4,":",REPT(" ",300)),"-",REPT(" ",300)),300,300))​

<fieldset class="postcontent" style="margin: 5px 0px; padding: 5px 10px; border-width: 0px; border-style: initial; border-color: initial; background-image: none; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial; border-radius: 5px;"></fieldset>

 

[Seba Robles]

This should help get you the text between the first and second colons ":"

=MID(A1,FIND(":",A1)+1,FIND(":",A1,FIND(":",A1)+1)-FIND(":",A1)-1)

 

[chris1979]

Thanks Seba..this worked too and simple too