Geography data types were introduced into Excel in 2018. They are only available in the Excel 365, Excel 2019 and Excel 2021.
What is a geography data type? Imagine that you have a cell or a range of cells containing country or town or city names, for example:
A
1 Australia
2 Brazil
3 United Kingdom
By converting these text entries to geographic data type entries you can display, in other cells, information about each country (or town or city) such as population, capital city (for countries) or language. These are known as Properties.
If you’re not familiar with geography data types, check out this video that I created on the subject. A couple of weeks ago someone posted a question in the comments section of the video. The question was about removing the word language from the Language property.
In this week’s YouTube video I cover how to do it. It might be a bit of a niche use case but in coming up with a solution, I demonstrate 2 useful Excel functions, ARRAYTOTEXT and SUBSTITUTE as well as explaining why, depending on the way you insert the languages, you might get SPILL errors and if you do, how to resolve them.
The formula that I used in the solution is:
=SUBSTITUTE(ARRAYTOTEXT(A2.\[Official language])," language","")
You can download a copy of the sample file by clicking through to YouTube. The link is in the YouTube description.