How to separate Unit Suite APT from an address in Excel

0 votes

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.

Nov 29, 2022 in Others by Kithuzzz
• 38,010 points
573 views

1 answer to this question.

0 votes

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. 

answered Dec 10, 2022 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer

How to open .xlsx files in MS Excel from VS Code?

Hello, to open xlxs files, or files ...READ MORE

answered Feb 17, 2022 in Others by gaurav
• 23,260 points
3,373 views
0 votes
1 answer

In Excel, how to find a average from selected cells

If one has the dynamic array formula ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 63,700 points
555 views
0 votes
1 answer

How to freeze an area in Excel?

Excel has a "Freeze panes" for this, ...READ MORE

answered Oct 17, 2022 in Others by narikkadan
• 63,700 points
523 views
0 votes
1 answer

How to stick an embedded document in a specific cell of an excel

Solution Select the documents (you can use the ...READ MORE

answered Oct 18, 2022 in Others by narikkadan
• 63,700 points
519 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
2,051 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,619 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
789 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
2,796 views
0 votes
1 answer

How to change where an email is sent FROM in Excel VBA

Inside the 'with out mail, insert this ...READ MORE

answered Mar 30, 2023 in Others by narikkadan
• 63,700 points
1,046 views
0 votes
1 answer

How to print an Excel Sheet using VBA with Nitro PDF Creator in Excel 2016

you can use the built-in excel facilities ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 63,700 points
1,027 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP