SOLVED

IF or Vlookup?

%3CLINGO-SUB%20id%3D%22lingo-sub-2261935%22%20slang%3D%22en-US%22%3EIF%20or%20Vlookup%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2261935%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20one%20of%20the%20cells%20in%20my%20spreadsheet%20there%20is%20an%20address%20field%20with%20Town%2C%20Region%20and%20Country.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20information%20i%20am%20trying%20to%20extract%20is%20the%20country%20that%20appears%20in%20the%20cell%20i%20is%20this%20possible%3F%20I%20have%20exhausted%20my%20limited%20knowledge%20and%20would%20welcome%20any%20advice.%20As%20an%20example%20please%20see%20below%20-%20the%20information%20that%20i%20need%20to%20be%20in%20the%20second%20column%20is%20the%20country%20(such%20as%20United%20States%20and%20Ireland%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22403%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22316%22%3EBandera%2C%20Texas%2C%20United%20States%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBandon%2C%20County%20Cork%2C%20Ireland%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBanff%2C%20Alberta%2C%20Canada%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBanff%2C%20Scotland%2C%20United%20Kingdom%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBangor%2C%20Maine%2C%20United%20States%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBangor%2C%20Northern%20Ireland%2C%20United%20Kingdom%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2261935%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2261990%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20or%20Vlookup%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2261990%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1020936%22%20target%3D%22_blank%22%3E%40MarkHSEPeople%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssuming%20your%20address%20is%20in%20A2%2C%20then%20try%20this...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20B2%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DTRIM(RIGHT(SUBSTITUTE(A2%2C%22%2C%22%2CREPT(%22%20%22%2CLEN(A2)))%2CLEN(A2)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eand%20copy%20it%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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  
4 Replies
best response confirmed by MarkHSEPeople (New Contributor)
Solution

@MarkHSEPeople 

 

Assuming your address is in A2, then try this...

 

In B2

=TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))),LEN(A2)))

and copy it down.

The output would be like this...

 

Address.jpg

@Subodh_Tiwari_sktneer Thank you VERY much for your help with this - your formula worked a treat! 

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.