USE EXCEL TO PARSE CITY – STATE – ZIP

If data export out of your old system does not separate the City, State, and ZIP Code fields, you can use Excel’s built in functions to do the job.

For example:

Column A: Display the exported City, State, and ZIP, as a single field. The following formulas work if there is a comma after the city, one space only after the comma, and one space after the state.

Column B: enter =FIND(“,”,A1)
This finds the position of the comma in column A

Column C: enter =MID(A1,1,B1-1)
This extracts all the letters to the left of the comma in column A. It will display the city.

Column D: enter =MID(A1,B1+2,2)
This extracts the 2 letters to the right of the space after the comma. It will display the state.

Column E: enter =MID(A1,B1+5,11)
This extracts the 11 numbers starting at 5 characters to the right of the comma. It will display the Zip Code.

After the entire set of columns is parsed, select all the columns, use the Copy command, and then use the Paste Special Command: Values Only. This will save the values, rather than the formulas, so they can be imported. You may need to adjust the columns and delete Column B. Then do a Save As so you do not overwrite the original spreadsheet with the formulas. Save as Text/Tab Delimited.

Please note that AG Systems does not offer support for Microsoft Excel. Further questions should be directed to the Microsoft help menus, web site, or tech support services.