Welcome to the community, @Ilan_Weinstein! :grinning_face_with_big_eyes: The SWITCH() function only operates with exact matches against the entire string you’re feeding it. That said, if the city you’re looking for is at the start of the address field, you could pass the first few characters of the string using the LEFT() function.
SWITCH(LEFT(Address, 5), ...
However, you’d need to pick a length that will work for all cities you’re looking for. In your example above, the shortest city names have four characters, so you couldn’t pass more than four characters to the SWITCH() function. If there are cities in different regions that start with the same four characters—e.g. Stockport and Stochington (making up that second name)—you won’t have accurate output because the SWITCH() function will go with the first one that it finds.
Something else to be aware of is that these comparisons are case sensitive. If you’re processing data entered by someone else and they typed “LONDON”, a search for “Lond” in the first four characters won’t locate it. A way around this is to force what’s passed to be all lower case using the LOWER() function.
With that understood, your example setup would look like this:
SWITCH(
LOWER(LEFT(Address, 4)),
"lond", "London",
"chin", "London",
"romf", "London",
"hale", "Manchester",
"stoc", "Manchester",
"bury", "Manchester"
)
The only way to look for a partial match in a string regardless of length (i.e. full city names) is with either the FIND() or SEARCH() functions, which won’t work with SWITCH(). For that you’d need to use a series of nested IF() functions.
There are probably other ways of pulling this off using automations and/or scripts. If you feel that you need to go down that route, I’m available for hire for custom scripting projects; PM for details.
