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 

[Laurap1228]

Hi @EvansM 

Try using the Text To Columns Tool with \s as the delimiter. I attached an example solution.

[Tyro_abc]

Hi @EvansM 

Try this

Regards

Arundhuti

[EvansM]

@Laurap1228,@Tyro_abc  Thank you very much for responding. I still got issue with the last address. Please see the example below.

30 Liberty Square Rd 37B

183587 Corral Del Cielo Rd  Apt #3

Expected Result should be 

str_nbr        strt_name            strt_ type_         Street_unit
30              Liberty Square             Rd                        37B

183587      Corral Del Cielo          Rd                       Apt #3

NOT 

str_nbr        strt_name             strt_ type_         Street_unit
 30                 Liberty                     Square                Rd  37B

183587          Corral                    Del Cielo              Rd Apt #3                      

[danilang · Accepted solution]

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

[Tyro_abc · Accepted solution]

Hi

added a "Cleanse" tool and slightly modified Regex, it seems to be working with your example. Please let me know if you have any questions.

[EvansM]

@Tyro_abc @danilang Thank you very much guys for you help. I think this will do a trick.