• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

extract countries from long string

an_n1

New Member
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
 

Attachments

pecoflyer

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

Code:
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"
 

Attachments

bosco_yip

Excel Ninja
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.

79618
 

GraH - Guido

Well-Known Member
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

Well-Known Member
office 365, cell B2:
Code:
=TRANSPOSE(FILTER(country!$A$2:$A$196,ISNUMBER(SEARCH(country!$A$2:$A$196,A2))))
or
Code:
=LET(a,country!$A$2:$A$196,TRANSPOSE(FILTER(a,ISNUMBER(SEARCH(a,A2)))))
or for one column results:
Code:
=LET(a,country!$A$2:$A$196,TEXTJOIN(", ",TRUE,FILTER(a,ISNUMBER(SEARCH(a,A2)))))
copy down.
 
Last edited:
Top