Parsing string by character limit

[aa12on]

Hello,

I am looking for some help to solve a data parsing issue.

I need to load some zip code data into one of our tools, but I have a field size limitation of 1000. Some of my data exceeds this limit, so I need to parse the zip code data into separate rows to stay within this limitation.

Here is an example of what my data looks like.

Territory Name	Zip Codes	Length
Territory 1	92320,92321,92322,92324,92325,92332,92335,92336,92337,92339,92342,92345,92346,92347,92350,92352,92354,92358,92359,92363,92364,92365,92368,92371,92372,92373,92374,92376,92377,92382,92392,92394,92395,92397,92399,92401,92404,92405,92407,92408,92410,92411,92501,92503,92504,92505,92506,92507,92508,92509,92518,92521,92530,92532,92536,92539,92543,92544,92545,92548,92549,92551,92553,92555,92557,92561,92562,92563,92567,92570,92571,92582,92583,92584,92585,92586,92587,92590,92591,92592,92595,92596,92602,92603,92604,92606,92610,92612,92614,92617,92618,92620,92624,92625,92626,92627,92629,92630,92637,92646,92647,92648,92649,92651,92653,92655,92656,92657,92660,92661,92662,92663,92672,92673,92675,92676,92677,92679,92683,92688,92691,92692,92694,92697,92701,92703,92704,92705,92706,92707,92708,92780,92782,92801,92802,92804,92805,92806,92807,92808,92821,92823,92831,92832,92833,92835,92840,92841,92843,92844,92845,92860,92861,92865,92866,92867,92868,92869,92870,92879,92880,92881,92882,92883,92886,92887,93001,93003,93004,93010,93012,93013,93015,93021,93022,93023,93030,93033,93035,93036,93041,93042,93043,93060,93063,93065,93066,93101,93103,93105,93106,93108,93109,93110,93111,93117,93510,93532,93534,93535,93536,93543,93550,93551,93552	1229

Here is what I want it to look like.

Territory Name	Zip Codes	Length
Territory 1	92320,92321,92322,92324,92325,92332,92335,92336,92337,92339,92342,92345,92346,92347,92350,92352,92354,92358,92359,92363,92364,92365,92368,92371,92372,92373,92374,92376,92377,92382,92392,92394,92395,92397,92399,92401,92404,92405,92407,92408,92410,92411,92501,92503,92504,92505,92506,92507,92508,92509,92518,92521,92530,92532,92536,92539,92543,92544,92545,92548,92549,92551,92553,92555,92557,92561,92562,92563,92567,92570,92571,92582,92583,92584,92585,92586,92587,92590,92591,92592,92595,92596,92602,92603,92604,92606,92610,92612,92614,92617,92618,92620,92624,92625,92626,92627,92629,92630,92637,92646,92647,92648,92649,92651,92653,92655,92656,92657,92660,92661,92662,92663,92672,92673,92675,92676,92677,92679,92683,92688,92691,92692,92694,92697,92701,92703,92704,92705,92706,92707,92708,92780,92782,92801,92802,92804,92805,92806,92807,92808,92821,92823,92831,92832,92833,92835,92840,92841,92843,92844,92845,92860,92861,92865,92866,92867,92868,92869,92870,92879,92880,92881,92882,92883,92886,92887	995
Territory 1	93001,93003,93004,93010,93012,93013,93015,93021,93022,93023,93030,93033,93035,93036,93041,93042,93043,93060,93063,93065,93066,93101,93103,93105,93106,93108,93109,93110,93111,93117,93510,93532,93534,93535,93536,93543,93550,93551,93552	233

I have attached the data details for these zip territories that are exceeding this 1000 character limitation.

I can't figure out how to solve this. Any advice out there would be appreciated.

[Luke_C]

hi @aa12on 

Here's my take on this. Essentially:

1. Split the data into one row per zip code
2. Use multi-row to create a running total that resets itself if it goes over 1000 (and accounts for the commas that will ultimately be included)
3. Use multi-row to create 'groups' to concatenate
4. Summarize tool to concatenate by territory/group

[AngelosPachis]

Hello @aa12on 

What I did was to parse each postcode string on the comma and then create a running total for the total string length, allowing 1 extra space for the trailing comma. Then I created groups of territories, that can fit up to 996 characters (if you allow more, then the total length will go beyond 1000, 1001 to be precise)

I also used a formula tool at the end to check if any string exceeds 1000 characters, it appears it doesn't

Hope that helps,

Angelos

[Tyro_abc]

Hi @aa12on 

Try this...

Best Regards

Arundhuti