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
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
With that understood, your example setup would look like this:
The only way to look for a partial match in a string regardless of length (i.e. full city names) is with either the
SEARCH() functions, which won’t work with
SWITCH(). For that you’d need to use a series of nested
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.