If a cell contains specific text then return a certain value

[jjkh58]

Hi guys,

Column C

C1: AS2_Receiver_AdvancedShipmentNotification

C2: AS2_Sender_AdvancedShipmentNotification

I have something like the above in my spreadsheet. I need to use the formula to tell me whether it's Receiver or Sender, so I used =IF(C1="Receiver","Receiver","Sender") formula to return receiver if the cell contains a word receiver otherwise sender. However, my formula does not work.

Please help me out.

Thanks,

 

[Trebor76]

Hi jjkh58,

The following will return what's in-between the first and second underscore or nothing if it errors out:

=IFERROR(MID(A1,FIND("^^",SUBSTITUTE(A1,"_","^^",1))+1,(FIND("^^",SUBSTITUTE(A1,"_","^^",2))-FIND("^^",SUBSTITUTE(A1,"_","^^",1)))-1),"")

Regards,

Robert

 

[Peter_SSs]

formula to return receiver if the cell contains a word receiver otherwise sender.

Based on that, & assuming underscore before/after like your samples, try

=IF(ISNUMBER(SEARCH("_Receiver_",C1)),"Receiver","Sender")

If the word always comes after 4 characters like your samples then possibly just this.

=IF(MID(C1,5,8)="Receiver","Receiver","Sender")