I would need to standardize the name of certain sites e.g. Sydney Office, NSW Office, Orange -> should all be Sydney.Same for other sites e.g. Brisbane office, QLD Exp -> should be Brisbane.
I am able to execute nested IF. Please suggest the modification in below written formula.
=IFERROR(IF(SEARCH("*Syd*",S2,1),"Sydney"),"No")1 Answer
You can nest IFs by putting a new IF function as the previous one's if_false argument. For example:
=IFERROR(IF(SEARCH("Syd",S3,1),"Sydney",IF(SEARCH("Bri",S3,1),"Brisbane")),"No")Alternatively, I recommend another table with the names and a lookup function such as VLOOKUP. Please check below the Sample table:
Actual Value | Standardize Value
====================|====================
Sydney Office | Sydney
NSW Office | Sydney
Orange | Sydney
Brisbane office | Brisbane
QLD Exp | Brisbane
