Google-sheets – Change all unique country abbreviations to country names and place in one cell

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-query

I need a formula that can add all the countries with that is in a column.

Example:
In sheet 'Export' I have the below countries of origin:

enter image description here

And in other sheet I need to show all the countries of origin and change the name from RO to Romania or SK to Slovakia, etc…

What I have until now is the formula:

=TEXTJOIN(", ",TRUE, IF(left(Export!Q1,2) = "RO" , "ROMANIA", IF(left(Export!Q1,2) = "US" , "USA", IF(left(Export!Q1,2) = "PT" , "PORTUGALIA", IF(left(Export!Q1,2) = "FR" , "FRANTA", IF(LEFT(Export!Q1,2) = "SK" , "SLOVACIA"))))))

But is not working. It only shows the first country found, not all of them.

Can anyone help?

Link: https://docs.google.com/spreadsheets/d/1-Iz0K-tKKjuur6eF3tvEiEJP6zLjm8tAl0RXZqOwX9M/edit?usp=sharing

Best Answer

EDIT (following OP's request)

And If I want it to show only one time the country? To summarize all of them and to appear only one time.

Like this " Romania , Slovakia , England " in one cell. The purpose is to search in column Q if is that country and if it is, then write it. I need to be written only one time, one country. From on sheet, column Q, what countries are they.

"To show only one time the country" use

=ArrayFormula(UNIQUE(IFERROR( 
           SWITCH(Export!Q1:Q, "RO", "Romania", 
                                "SL", "Slovakia", 
                                "EU", "European Union",
                                "GB", "England"))))

To show "Like this Romania , Slovakia , England in one cell" try

=ArrayFormula(JOIN(", ",UNIQUE(QUERY(IFERROR( 
           SWITCH(Export!Q1:Q, "RO", "Romania", 
                                "SL", "Slovakia", 
                                "EU", "European Union",
                                "GB", "England")), "where Col1 is not null"))))

So what you want is to change the country abbreviations from RO, SK, EU, GB to Romania, Slovenia, European Union, England etc.

Please use the following formula.

=ArrayFormula(IFERROR( 
           SWITCH(Export!Q1:Q, "RO", "Romania", 
                                "SL", "Slovakia", 
                                "EU", "European Union",
                                "GB", "England")))

enter image description here

(Do adjust the ranges and following the pattern provided add more countries according to your needs)

Functions used:

Related Topic