Can't say the expressions given below are the best. You might be able to find better elsewhere. They are based on the following rules:
- Street address is everything before the line feed. It does not include the line feed.
- City is everything after the line feed and before the comma. The city name can be multiple words. It does not include the line feed or comma.
- State is all non-digits after the carriage return, town name, comma, and a single space and does not include the single space before the zip code. The state name can be multiple words.
- ZIP is five digits a dash and 4 more digits at the end of the string or is 5 digits at the end of the string. It tries for 5+4 first.
Extraneous spaces won't cause any harm to extracting the street, city, or state but they will be extracted as part of whichever piece they are in. For instance, you could end up with a city or state that has a trailing space character. You can put TRIM(...) around the formulas to strip off any extraneous leading, trailing, and repeated spaces within the strings.
Street C2=REGEX.EXTRACT(B2,".+(?=\n)")
City D2=REGEX.EXTRACT(B2,"(?<=\n).+(?=,)")
State E2=REGEX.EXTRACT(B2,"(?<=\n"&D2&", )\D+(?= )")
ZIP F2=REGEX.EXTRACT(B2,"(\d{5}-\d{4})|(\d{5})")
