CODEWITHSUNDEEP
google-sheetssplit
ssin
ssin

Reputation: 221

Extract last word from strings

In Google Sheets I have string values in cells like so:

1 Gheringhap Street, Geelong VIC 3220, Australia

I want to extract the country value from the string. The trouble is the string values have different numbers of commas so it's hard to say at which position the last value of the cell will be. I've tried: 

=INDEX(SPLIT(A2, ","), 1, 3)

But that only works if there are exactly two commas, if there are more or less commas then the query fails.

Is there any other way to get the value of a string in a cell?

Upvotes: 4

Views: 9237

Answers (1)

pnuts
pnuts

Reputation: 59485

There are many way to do so. One is to split on spaces and discard what is not required. Another is with a formula such as conventional in Excel (count the spaces by comparing the length of the string in full and after substituting spaces with nothing) and feed that as a parameter into a function that will skip to that instance in the string, but in Google Sheets the easiest way may be with regexextract eg: 

 =REGEXEXTRACT(A1,".+\s(.+)")

This creates an extract group (inside the inner parentheses) of any character . any number of times + that follows after any character any number of times and then a space \s.

Replace , by ; if that is what is required by the locale of your spreadsheet.

Upvotes: 12

Related Questions