• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract country from a field of text

Excel_beginner

New Member
I have a dataset with 4600 rows. From the location column, which could contain text like this: "Douglas F4D Skyray, Naval Air Station Point Mugu, California, USA"
I wish to extract the country name.
My first solution was to use text to column. However, this doesn't work well since some rows also include state name. This means that the final column with country name will be empty for those rows that have state name.
This is a sample how it can look like. The positive thing is that country name is always last. How can I extract information from the location row and store it in a new column that only shows the country name?
 
Last edited:
Try,

In B2, formula copied down :

=TRIM(MID(SUBSTITUTE(", "&$A2,", ",REPT(" ",99)),298,99))

Or, for longer wording.

=TRIM(MID(SUBSTITUTE(", "&$A2,", ",REPT(" ",LEN($A2))),LEN($A2)*3,LEN($A2)))

80401
 
Try,

In B2, formula copied down :

=TRIM(MID(SUBSTITUTE(", "&$A2,", ",REPT(" ",99)),298,99))

Or, for longer wording.

=TRIM(MID(SUBSTITUTE(", "&$A2,", ",REPT(" ",LEN($A2))),LEN($A2)*3,LEN($A2)))

View attachment 80401
Thanks for taking the time to test a solution. Unfortunaltely, this gives the exact same result as text to columns. For example, in B3, I want it to say USA and not California.

Can you explain what this code does? =TRIM(MID(SUBSTITUTE(", "&$A2,", ",REPT(" ",99)),298,99))
What are 99,298?
 
Thanks for taking the time to test a solution. Unfortunaltely, this gives the exact same result as text to columns. For example, in B3, I want it to say USA and not California.

Can you explain what this code does? =TRIM(MID(SUBSTITUTE(", "&$A2,", ",REPT(" ",99)),298,99))
What are 99,298?
So,

Change the formula in B2 to :

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

and copied down

80405
 
Last edited:
You might know that California is a state of the USA but, for a formula to work, it need to be instructed to return USA when the location ends in 'California' or 'Florida'
Code:
= LET(
     stateCountry, MAP(location, LAMBDA(first, TAKE(TEXTSPLIT(first, ","),,-1))),
     XLOOKUP(TRIM(stateCountry), state, country)
  )
where 'state' and 'country' provide a lookup table.
80407
 
Last edited:
Back
Top