How to parse Address Line 1 and Line 2 out of a field?

[gc-804]

I have an Excel data file that has address in one field with multiple lines for address line 1, address line 2, city/province/postal code, country. How to parse address line 1 and line 2 out of that address field and place each in a separate column but keep address line 2 blank if the record does not have an address line 2? Below are two made-up examples, one with and the other without the address line 2. Appreciate any help you could provide. Thank you!

Name	Address
Jane Doe	25 Mary Glover Avenue Markham, ON L8C 3B4 Canada
John Smith	1900 King's Park Avenue Unit 366 Toronto, ON M2R 1R9 Canada

[CharlieS · Accepted solution]

Hi @gc-804 

In the example image above, the fact that the different parts of the address are on different lines means that there are "newline" characters in there that can be used to parse it apart. Try using a Text To Columns tool with the delimiter set to "\n" to see how that field can be split up.

I've attached an example to show how this can be done. I also included a few more tools to give an example of how you could use the contents of each field to determine what address part it could be and transform the data into address fields for geocoding. 

[gc-804 · Accepted solution]

Hello @CharlieS,

Thank you for the quick reply! I tried using (.+)\n(.+) in Regular Expression in the attempt to just get the address line 1 and 2. It successfully parsed address line 1, but then address line 2 has all the data for country. So, I tried (.+)\n(.+)\n(.+)\n(.+) to break the address into 4 columns. Now records that have address line 2 have all been correctly parsed. However, records that don't have address line 2 returned only Nulls in the parsing results.  I am new to RegEx and would appreciate any help you or anyone could provide on getting the parsing results for both records with/without address line 2. Thank you!

[gc-804]

Hi @CharlieS,

Please ignore my last message as I did not see the last part of your response before I sent. Thank you so much for your help!! That's exactly what I need.