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

Question

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!

NameAddressJane Doe25 Mary Glover Avenue
Markham, ON L8C 3B4
CanadaJohn Smith1900 King's Park Avenue
Unit 366
Toronto, ON M2R 1R9
Canada

CharlieS · Accepted Answer

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 "
" 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 Answer

Hello @CharlieS,

Thank you for the quick reply! I tried using (.+)
(.+) 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 (.+)
(.+)
(.+)
(.+) 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!