Exclude certain text from formula result

[drewberts]

Hi all

I have the following in a table on one sheet

Col A Col B Col C
123 Acacia Road London NW1 3BD
456 Acacia Road London No Postcode
789 Acacia Road London SW7 6GH
000 Acacia Road London N/A
963 Acacia Road London NE34 ???

On another sheet I have a formula that simply strings each column data into a single text string e.g. (Col A, Col B, Col C) which results in the following:-

123 Acacia Road, London, NW1 3BD
456 Acacia Road, London, No Postcode
789 Acacia Road, London, SW7 6GH
000 Acacia Road, London, N/A
963 Acacia Road, London, NE34 ???

I'd like to add into the formula ways of excluding certain text strings in Column C (i.e. "No Postcode", N/A & anything "?" so the result ends up as:-

123 Acacia Road, London, NW1 3BD
456 Acacia Road, London
789 Acacia Road, London, SW7 6GH
000 Acacia Road, London
963 Acacia Road, London

Thanks

 

[sxhall]

Try this...

=TEXTJOIN(", ",,A1,B1, IF(OR(C1=" N/A",C1=" No Postcode"),"",IF(ISERROR(SEARCH("~?",C1,1)=0),C1,"")))

Omits anything with an "?" as well as the "N/A" and "No Postcode"

 

[Scott Huish]

=TEXTBEFORE(A1&" "&B1&", " &C1,{", No Postcode",", N/A","?"},,,1)

Copy down.

 

[drewberts]

Thanks guys - much appreciated