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
• 27,940 points
59 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
• 51,600 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
• 22,940 points
552 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
• 51,600 points
98 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
• 51,600 points
73 views
0 votes
1 answer

How to find out how many rows and columns to read from an Excel file with PHPExcel?

Solution: $file_name = htmlentities($_POST['file_name']); $sheet_name = htmlentities($_POST['sheet_name']); $number_of_columns = htmlentities($_POST['number_of_columns']); $number_of_rows ...READ MORE

answered Oct 23, 2022 in Others by narikkadan
• 51,600 points
1,366 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,640 points
206 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,640 points
373 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,640 points
168 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
• 22,940 points
149 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
• 51,600 points
227 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
• 51,600 points
148 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