Filter through a column and pick those cells that do not contain a specific word

[mstgier]

Good morning,

I try to figure out a formula, how I can filter a column, that has multiple words and I need to filter them on such way,
that the output should not contain a specific word.

For example:

Afghanistan Asia Kabul
Albania Europe Tirana
Algeria Africa Algiers
Andorra Europe Andorra la Vella
Angola Africa Luanda
Antigua and Barbuda America St. John's
Argentina America Buenos Aires
Armenia Asia Yerevan
Australia Oceania Canberra
Austria Europe Vienna
Azerbaijan Asia Baku
Bahamas America Nassau
Bahrain Asia Manama
Bangladesh Asia Dhaka
Barbados America Bridgetown
Belarus Europe Minsk
Belgium Europe Brussels
Belize America Belmopan
Benin Africa Porto Novo
Bhutan Asia Thimphu
Bolivia America Sucre
Bosnia and Herzegovina Europe Sarajevo
Botswana Africa Gaborone
Brazil America Brasilia
Brunei Asia Bandar Seri Begawan
Bulgaria Europe Sofia
Burkina Faso Africa Ouagadougou
Burundi Africa Bujumbura
Cambodia Asia Phnom Penh
Cameroon Africa Yaound?
Canada America Ottawa
Cape Verde Africa Praia
Central African Republic Africa Bangui
Chad Africa N'Djamena
Chile America Santiago
China Asia Beijing
Colombia America Bogota
Comoros Africa Moroni
Costa Rica America San Jos?
Croatia Europe Zagreb
Cuba America Havana
Cyprus Europe Nicosia

If we take the above list, I would like to List/Filter all the countries, which do not contain the word "America".

Doing some research, it tells me, that the new Filter function in O365 can't be used with Wildcards in combination with "does not contain". Does someone have a trick around this riddle?

Maybe it is very simple and there is a specific formula for this but I am not aware of it.
Of course I could take the other way around and just match what is Europe or what is Asia but the challenge shall be, "is not" or <> with wildcard or a workaround.

Thank you very much,

Mike

[wk9128]

pls try this formula

Please Login or Register  to view this content.

[mstgier]

Dear wk9128,
That is fantastic and works. I forgotten about the NOT. I even can use the wildcards, therefore perfect for me,

Thanx so much for the help!!!

[wk9128]

@mstgier You're Welcome. Glad to help . Thank You for the feedback and rep.