Need help parsing 1 field containing County names and Zips codes

[Average_Badger]

I need a way to parse out the values in a field that contains multiple county names on some lines, along with Zip codes.  I also need to match these county names and zip codes with their associated States and 'LOB_DESC' codes.  County names need to go on one file and zip codes on another.  I know there is a 'Text to Column' Tool and i was thinking of a way to use that.  

I included a sample shot of the data to get a better idea of what im talking about.

Any advice or ideas would be greatly appreciated.

Thanks!

[patrick_digan]

@Average_Badger I think the text to columns would be the right tool! I'm assuming that you always have counties list first, each one separated by a comma. If zips are listed as well, I'm assuming there's a : separating them from counties. I've attached a quick sample. Basically use the text to columns to split the counties and zips with the : delimiter into separate fields. Then use text to columns on both the county and zip field to split it into rows using the , delimiter.

Let me know if that doesn't do it!

[Average_Badger]

Thanks for the quick reply!  Yes, most of the values are separated by commas, but it also looks like there are some : in there between a county name and a zip code (EX: 'CLARK:89138, 89144,89143 etc')

These are Counties and their associated zip codes within those counties.  Perhaps i can do this in two steps and parse just the counties separated by commas, then deal with the zips and counties.

[patrick_digan]

I think my solution should handle both counties and zips like you're describing. It just wouldn't handle a situation where you have something like: Clark:12345,David:56789. I didn't see anything like that in your sample data you posted.

[Average_Badger]

Here is an example of what i was talking about.  These are mixed in with the counties separated by commas.

[patrick_digan · Accepted solution]

@Average_Badger Here is a picture of the workflow I attached:

The first text to columns tool is using : as a delimiter and splitting the data into 2 columns. So in your case, it would put Santa Barbara in one column and all the zips in the next column. Then I have 2 separate text to columns tools to handle the counties (the first column) and zips (the second column) where it's splitting them into rows using the , delimiter. So in your case all those zips would then get split into multiple rows. in the second text to columns.

[Average_Badger]

Fantastic!  I will try this out and see what i can do.  I appreciate it!

[Average_Badger]

This works great, thank you so much for help!