How to split Data rows

EvansM

Hello

Can please somebody help me with this scenario. I have addresses rows I want to sprit it.

Example:      123  Newstrade Rd   Apt 3

                     456  Testscenario Lane  # 4

                     789   Amazon  Trail   Apt 254

                     1015 Jumphigh  Ave

                      45 8th St # 801

                      30 Liberty Square Rd 37B

Expected Result should be 

 

str_nbr        strt_name            strt_ type_         Street_unit

123              Newstrade                 RD                     Apt 3

456             Testscenario             Lane                     # 4

789              Amazon                   Trail                    Apt 254

1015           Jumphigh                 Ave

 45               8th                           St                       # 801

30                Liberty Square       Rd                         37B

Thanks in advance 

danilang

Hi @EvansM 

Parsing addresses is complex because of the varying number of words in street names.  To counter this, you have to build a workflow that works based on the constants that you can predefine.  In your case, street number is always the first column and street type is one of a fixed number of possible values.  Using these facts we can build a workflow that assigns the other values based on their positions relative to these two

Start with your input, add a recordID, split to rows on space and then add a column number so we can sort as required.  the bottom Text Input tool contains a list of possible street type identifiers, St, Rd, Trail, etc.  Join this to the split data to mark the street type values.  for the ones that join, add a ColumnName field called strt_type and union these records wiht the unjoined ones.  After a Sort, use a Multi-Row Formula tool to map out the sub address fields adding values to the ColumnName field as required.  Crosstab and rearrange the columns to give you

Dan

AddressParsing.yxmd