extract countries from long string

[an_n1]

Hi everybody,
I need your help with the file attached
I have a column consisting addresses with one or more than 1 countries
I want to exctract the countries either in 1 column, or more than 1 columns doesn't matter
For instance the cell is
"University of Lincoln, United Kingdom; Universidade Federal de Lavras, Departamento de Química, Caixa Postal 3037, Lavras, MG, 37200-000, Brazil"
I expect the result in column to be United Kingdom, Brazil
In short i want the excel to automatically produce the country/countries name from the string text
Any help is much appreciated
Thank you

 

[pecoflyer]

If you have Power Query
After adding rows, select Table Design - Refresh All

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Data", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Data.1", "Data.2", "Data.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Data.1", type text}, {"Data.2", type text}, {"Data.3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns", "Value", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, true), {"Value.1", "Value.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Value.1", type text}, {"Value.2", type text}})
in
    #"Changed Type2"

 

[bosco_yip]

Try this formula solution,

This formula worked for Excel 2019 or above

In B2, array ("Ctrl+Shift+Enter") formula copied down :

=TEXTJOIN(", ",,FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE($A2&";",";","<r/>,"),",","</b><b>")&"</b></a>","//b[r][not(preceding::*=.)]"))

Remark : If you have Office 365, array entry is not required, and could use normal entry.

 

[GraH - Guido]

Alternative,
Named the range holding the list of countries, duh "Countries"....

[B2]=TEXTJOIN("," ,TRUE, IFERROR(INDEX(Countries,ROW(Countries)/COUNTIF(A2,"*"&Countries&"*")),""))

Might need CTRL+SHIFT+ENTER on versions, from 2016 onwards, that are not 365.

 

[p45cal]

office 365, cell B2:

=TRANSPOSE(FILTER(country!$A$2:$A$196,ISNUMBER(SEARCH(country!$A$2:$A$196,A2))))

or

=LET(a,country!$A$2:$A$196,TRANSPOSE(FILTER(a,ISNUMBER(SEARCH(a,A2)))))

or for one column results:

=LET(a,country!$A$2:$A$196,TEXTJOIN(", ",TRUE,FILTER(a,ISNUMBER(SEARCH(a,A2)))))

copy down.