CODEWITHSUNDEEP
regexgoogle-sheetsgoogle-sheets-formulaarray-formulas
electrophile
electrophile

Reputation: 305

Matching partial text at the end of a string in Google Sheets

I have data in a Google spreadsheet that I need to match to their standard country codes. For e.g. Greater Chicago Area would be US. For all cells that need to be marked US, they seem to end with the word Area. For all other countries, they end with their country names.
I tried the following formula but it does not seem to work -

=IFS(ISNUMBER(FIND("Area",A64)), "US", ISNUMBER(FIND("*United Kingdom",A64)), "UK").

Is there a way to match partial text at the end of the string?

Upvotes: 5

Views: 12246

Answers (3)

marikamitsos
marikamitsos

Reputation: 10573

You can also try this single formula

=INDEX(IFERROR(REGEXREPLACE(
                      REGEXREPLACE(A2:A,"Canada Area","Canada"),
                               " Area$|, TN$",", US")))

enter image description here

How does the formula work?

The inner REGEXREPLACE finds the expression Canada Area within the text and replaces it with just Canada.
Then, the outer REGEXREPLACE finds where the very last Area or TN string and replaces them with , US (we can further enrich the formula with more conditions if necessary)
Finally the combination of INDEX and IFERROR expands our formula to the whole range excluding errors (which are now returned as empty cells).
As for the regex symbols used, $ defines the end of a line and | the OR statement.

Functions used:

Upvotes: 0

Nathaniel Kam
Nathaniel Kam

Reputation: 121

Here is the answer if you just wanted it by name, I didn't use country codes but you can easily just do a find replace search OR define a vlookup from another range.

Answer Spreadsheet with Formulas Listed in Column Header - https://docs.google.com/spreadsheets/d/1KOYWwqY_T8aG0USy5POVLjAdl99N2PrwWs5mpQAruRg/edit?usp=sharing

Steps:

  1. Paste Region Data from your pastebin
  2. Does the string Ends in "Area" using =right(A2,4)="Area" // Let's us know almost all cases that are US
  3. Find Comma Index using =find(",",A2) // Let's us find last word in array
  4. Find Char Right of Comma using =LEN(A2)-C2 // Tells how many letters are to the right of the comma (the last word + one space)
  5. Subtract 1 for the space using =D2-1 // You can use Trim() for non standardized data but for your use case -1 works fine
  6. Everything Right of the Comma using =right(A2,E2) // The last word
  7. Country Name using =if(B2,"United States of America",F2) // If it ends in area set to US otherwise set it to last word in string
  8. Special Case Handling using =if(ISERR(F2),G2,if(G2="TN","United States",if(G2="TN","United States of America",if(G2="Zhejiang, China","China",if(F2="Canada Area","Canada",G2))))) // Your special cases that need to be overridden.
  9. Vlookup for country code =vlookup(H2,'Country Codes'!A:B,2,false) /// Will find the country code in column S of searched range based on KEY provided.

If you need to clean this up for a presentation you can just replace ALL cell references with the formula in that cell until you nest all the way back to just column A.

Upvotes: 6

sharken
sharken

Reputation: 57

Your formula seems to be okay. Please try and place this FIND condition at the last, after checking for every other country code.

ISNUMBER(FIND("Area",A64)), "US"

enter image description here

Upvotes: 0

Related Questions