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
Solved! Go to Solution.
Hi @EvansM
Try using the Text To Columns Tool with \s as the delimiter. I attached an example solution.
@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
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