How to Clean and Dedupe a Local Lead List
Merge your exports into one sheet, remove duplicates on the listing ID first, then on phone and website domain. Split the address into parts, standardize phone numbers, strip review counts to numbers, and flag rows with missing or odd data before you import or reach out.
Quick Answer: Merge your exports into one sheet, remove duplicates on the listing ID first, then on phone and website domain. Split the address into parts, standardize phone numbers, strip review counts to numbers, and flag rows with missing or odd data before you import or reach out.
Merge your files
Put every export in one sheet with the same column order, and add a Source column with the search that produced each row (for example, plumber 80205). It helps later when you judge which searches were worth it.
Remove duplicates in the right order
- ID: Bing's listing ID is the most reliable key. In Excel, select the data, then Data > Remove Duplicates and tick ID only. In Google Sheets, Data > Data cleanup > Remove duplicates.
- Phone: after standardizing phones (below), dedupe again on phone to catch the same business listed twice.
- Domain: dedupe on website domain to group multi-location businesses. Don't delete these blindly; locations may be separate prospects.
Split the address
US addresses in the export look like 1000 E 5th St Ste 110, Austin, TX 78702.
- Use Data > Text to Columns with comma as the delimiter to get street, city, and state plus ZIP.
- ZIP: =RIGHT(TRIM(C2),5)
- State: =LEFT(TRIM(D2),2) on the state-plus-ZIP part.
Check rows that don't fit the pattern, such as addresses with no street.
Standardize phones
The export shows US phones in local format, like (512) 607-4268. For dialers and CRMs, convert to E.164:
- Excel 365: ="+1"&TEXTJOIN("",TRUE,IFERROR(MID(M2,SEQUENCE(LEN(M2)),1)*1,""))
- Google Sheets: ="+1"®EXREPLACE(M2,"[^0-9]","")
Only prefix +1 for US and Canadian numbers.
Reduce websites to domains
In Google Sheets: =REGEXEXTRACT(L2,"^(?:https?://)?(?:www.)?([^/]+)"). A website such as swishsmiles.com/location/... becomes swishsmiles.com, which makes multi-location groups easy to see.
Clean ratings and review counts
Rating Info comes as (24). Turn it into a number with =VALUE(SUBSTITUTE(SUBSTITUTE(I2,"(",""),")","")).
Flag rows to check
Add a Check column and flag rows with no phone, no website, a person's name as the business name, or an address without a street. Review those by hand.
Frequently Asked Questions
What's the best column to dedupe on?
The ID column, because it's Bing's listing ID. Then phone, then domain.
Should I delete businesses with the same website?
Not automatically. Multi-location businesses share a domain but each location can be a separate prospect.
