I found the formula from another thread:

=IF(COUNTIF(AO5,"*tokyo*")0,"Tokyo","nil")

Thanks

"Tracey" wrote:

Hello,

I have a column which contains names of Cities...
eg: cell a1= Tokyo, Hong Kong, New York
cell a2= London, Amsterdam, berlin
cell a3= London, Tokyo, New York, Madrid

is there a formula that will be able to pull out Tokyo

I was thinking of an IF statement: =IF(cell contains "Tokyo", "Tokyo", " ")
not sure how to go forward without going down the filtering route as it's a
huge spreadsheet and time is quite critical in reporting season.

many thanks