Apr 08 2021 11:19 AM
In one of the cells in my spreadsheet there is an address field with Town, Region and Country.
The information i am trying to extract is the country that appears in the cell i is this possible? I have exhausted my limited knowledge and would welcome any advice. As an example please see below - the information that i need to be in the second column is the country (such as United States and Ireland
Bandera, Texas, United States | |
Bandon, County Cork, Ireland | |
Banff, Alberta, Canada | |
Banff, Scotland, United Kingdom | |
Bangor, Maine, United States | |
Bangor, Northern Ireland, United Kingdom |
Apr 08 2021 11:32 AM
Solution
Assuming your address is in A2, then try this...
In B2
=TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))),LEN(A2)))
and copy it down.
Apr 09 2021 01:50 AM
@Subodh_Tiwari_sktneer Thank you VERY much for your help with this - your formula worked a treat!
Apr 09 2021 02:51 AM
You're welcome @MarkHSEPeople! Glad it worked as desired.
Please take a minute to mark the post with the proposed solution as a Best Response to mark your question as Solved.
Apr 08 2021 11:32 AM
Solution
Assuming your address is in A2, then try this...
In B2
=TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))),LEN(A2)))
and copy it down.