Change all unique country abbreviations to country names and place in one cell

Question

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:

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

Answer 1

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")))

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

Functions used:

• ArrayFormula
• IFERROR
• SWITCH

Answer 2

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.