How to separate Unit Suite APT from an address in Excel

Question

I have a database of 272,000 addresses But some addresses have unit/suite/STE/APT see the example below:

16 BRIARWOOD COURT UNIT B MONTVALE, NJ 07645 100 CROWN COURT #471 EDGEWATER, NJ 07020 23-05 HIGH ST APT A FAIR LAWN, NJ 07410 15-01 BROADWAY STE 6 FAIR LAWN, NJ 07410 80 BROADWAY, SUITE 1A CRESSKILL, N.J. 07626 300 GORGE ROAD APT 11 CLIFFSIDE PARK, N.J. 07010

I would like to split the text to the next column when it comes across unit/suite/STE/APT.

I want to separate these so I can use an Advance filter with unique records and create a master find and replace to clean the list.

narikkadan · Answer 1 · Dec 10, 2022

Use batch geocode your file on geocoder.ca

This is the result I got:

rawlocation Latitude Longitude Score StandardCivicNumber StandardAddress StandardCity StandardStateorProvinceAbbrv PostalZip Confidence
16 BRIARWOOD COURT UNIT B MONTVALE NJ 07645 41.035587 -74.06744 1
16 Briarwood Crt Montvale NJ 7677 0.7
100 CROWN COURT #471 EDGEWATER NJ 07020 40.822893 -73.978375 1 100 Crown Crt Edgewater NJ 07020-1137 0.8
23-05 HIGH ST APT A FAIR LAWN NJ 07410 40.940276 -74.120329 1 23 High St Fair Lawn NJ 07410-3574 0.8
15-01 BROADWAY STE 6 FAIR LAWN NJ 07410 40.920501 -74.091153 1 1 S Broadway Fair Lawn NJ 07410-5529 0.8
80 BROADWAY - 0
300 GORGE ROAD APT 11 CLIFFSIDE PARK N.J. 07010 40.814151 -73.990015 1 300 Gorge Rd Cliffside Park NJ 07010-2759 0.8

From the cleaned-up version, you can then street compare to extract additional entities. I hope this helps you.