Extract string

[Portucale]

Hi,

I need to extract a part of string text which is between ":", but I can't figure out the best way, the example below:

CL31T : Strategic_Dialler_File Callback : Scheduled Date and Time : 23/05/2014 12:00
CL31T : Strategic_Dialler_File Callback : Scheduled Date and Time : 30/05/2014 10:31
CL31T : Strategic_Dialler_File Non Telemarketable : Active Cable Customer
CL31T : Strategic_Dialler_File Callback : Scheduled Date and Time : 22/05/2014 15:00
CL31T : Strategic_Dialler_File Callback : Scheduled Date and Time : 17/05/2014 15:00
CL31T : Strategic_Dialler_File Refusal : Offer rejected/Unwilling to discuss
CL31T : Strategic_Dialler_File Sale : Sale

The results would be as below;

Strategic_Dialler_File Callback
Strategic_Dialler_File Callback
Strategic_Dialler_File Non Telemarketable
Strategic_Dialler_File Callback
Strategic_Dialler_File Callback
Strategic_Dialler_File Refusal
Strategic_Dialler_File Sale

I can extract everything on the right of the first 'colon' but not in between the 'colons'

Any help and all the help is very much appreciated,

 

[Debraj]

Hi Prtucale..

Just in case.. if you want to extract the nth word between a specified delimeter.. try this..

=TRIM(MID(SUBSTITUTE($A1,":",REPT(" ",99)),((2-1)*99)+1,99))

where Delimeter is in RED, and nth Word is in BLUE..

Increase this 99, in case of very long word..

 

[Portucale]

Hi Prtucale..

Just in case.. if you want to extract the nth word between a specified delimeter.. try this..

=TRIM(MID(SUBSTITUTE($A1,":",REPT(" ",99)),((2-1)*99)+1,99))

where Delimeter is in RED, and nth Word is in BLUE..

Increase this 99, in case of very long word..

Thanks Debraj, works like a treat and saved a few hours of work