Breaking up a variable with city, state, and zip

You might have a difficult time breaking up a city, state, and zip code into three variables if they are on a single line because city names have variable lengths. This post shows an effective way to reliably create three variables from lines such as these:

• Gary, IN 46402
• Rancho Santa Margarita, CA 92688

1. Create a variable that includes the city, state and zip codes named CityStateZip.
2. Create variables named Zip, State, and City. Right click each variable > Add Function > Set value and set the values to CityStateZip:
3. Right click the variable Zip > Add Function > Substring. Enter a starting position of -1, which means to start on the right side and count characters going left. We know our data contains only five-digit postal codes, so the number of characters is 5. Click OK, and the Zip variable is set.
   
4. Right click the variable State > Add Function > Substring. The state data uses two-letter abbreviations, so we use the same function that we used in step 3, but change the number of characters to 8. The State variable now includes the state and zip.
5. Add another Substring function, this time starting at position zero (0) for a length of two characters. Click OK, and the State variable is set.
6. Right click the City variable > Replace > and set the first value to a comma (,), and set the place string to 20 to 30 spaces. This replacement creates many spaces after the city name.
7. Add a substring function starting at position 0 and continuing for as many characters as your longest city name. Click OK, and the City variable is set.
8. Optionally, you can add a Right Trim function the the City variable to remove trailing spaces. The following Variables screen shots show the results for a city name as small as Gary and as long as Rancho Santa Margarita: