Find and Concatenate excluding <text>

[Kelli Webb]

Long time no see!

Hi Guys,

If anyone can assist with this it would be greatly appreciated.

Desired result is in column D.

kind regards,

Kelli

 

[bosco_yip]

Maybe,

In D2, copied down :

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2&" and "&B2,"<Not Defined> and ","")," and <Not Defined>",""),"<Not Defined>","")

Regards
Bosco

 

[vletm]

Kelli Webb
Something like this?

 

[Kelli Webb]

Maybe,

In D2, copied down :

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2&" and "&B2,"<Not Defined> and ","")," and <Not Defined>",""),"<Not Defined>","")

Regards
Bosco

Sorry Bosco, I've been advised otherwise. If both fields are <Not Defined>, please show as <Not Defined>.

Cheers!

 

[shrivallabha]

Use:
=SUBSTITUTE(SUBSTITUTE(CONCATENATE(A2," and ",B2)," and <Not Defined>",""),"<Not Defined> and ","")