Google Sheets is the unsung hero of B2B data enrichment. With the right 15 formulas, you can clean, validate, dedupe, and enrich your prospect list without touching a single line of code or paying for a CRM add-on.

Here are the 15 Google Sheets formulas every B2B team should master in 2026.

The 5 cleanup formulas

1. CLEAN + TRIM

=TRIM(CLEAN(A1)) - removes invisible characters + trailing spaces. The #1 cause of duplicate-looking rows.

2. PROPER

=PROPER(A1) - fixes "john SMITH" → "John Smith". Essential before any merge mail.

3. SUBSTITUTE

=SUBSTITUTE(A1, "Inc.", "") - strips legal suffixes for cleaner company names matching.

4. LOWER

=LOWER(A1) - normalize emails before dedup.

5. REGEXREPLACE

=REGEXREPLACE(A1, "[^a-zA-Z0-9@.]", "") - strip everything that's not alphanumeric, @, or dot. Bullet-proof email cleanup.

The 5 validation formulas

6. REGEXMATCH (email)

=REGEXMATCH(A1, "^[^@\s]+@[^@\s]+\.[^@\s]+$") - returns TRUE if the cell is a valid email syntax.

7. ISEMAIL (built-in)

=ISEMAIL(A1) - built-in, simpler. Doesn't catch all edge cases but 90% of them.

8. LEN check for phone

=AND(LEN(A1)>=10, LEN(A1)<=15) - international phone lengths.

9. ARRAYFORMULA for batch validation

=ARRAYFORMULA(IF(LEN(A2:A)=0, "", ISEMAIL(A2:A))) - validates entire column in one cell, no copy-down.

10. COUNTIF for duplicates

=COUNTIF(A:A, A2)>1 - flags duplicates in column A.

The 5 enrichment formulas

11. VLOOKUP for company match

=VLOOKUP(B2, CompanyDB!A:D, 3, FALSE) - match a company name to firmographic data in a reference sheet.

12. IMPORTXML for website scraping

=IMPORTXML(A1, "//meta[@name='description']/@content") - extracts meta description from any URL.

13. CONCATENATE for full name

=A2&" "&B2 - builds full name from first/last columns.

14. SPLIT for email parsing

=SPLIT(A2, "@") - splits email into [local, domain] for company extraction.

15. IFERROR wrapper

=IFERROR(your_formula, "") - never let a failed lookup break your sheet.

Key takeaways

  • 15 formulas cover 90% of B2B data cleanup, validation, and basic enrichment in Google Sheets.
  • Cleanup formulas (CLEAN, TRIM, PROPER, SUBSTITUTE, REGEXREPLACE) prevent dedup nightmares.
  • Validation formulas (REGEXMATCH, ISEMAIL, COUNTIF) catch bad data before it hits your CRM.
  • Enrichment formulas (VLOOKUP, IMPORTXML, SPLIT) bring external data in without a code editor.
  • Always wrap volatile formulas (IMPORTXML, lookups) in IFERROR to avoid #REF cascades.

Frequently asked questions

What is the best Google Sheets formula to validate email addresses?
ISEMAIL is the simplest option: it is built-in and catches about 90% of cases. For stricter syntax checking, use REGEXMATCH with a pattern like "^[^@\s]+@[^@\s]+\.[^@\s]+$". Wrap either one in ARRAYFORMULA to validate an entire column from a single cell instead of copying the formula down.

How do I find duplicates in a Google Sheets lead list?
Use =COUNTIF(A:A, A2)>1 to flag any value that appears more than once in the column. Before running it, normalize your data: LOWER on emails and TRIM(CLEAN()) on text cells. Invisible characters and trailing spaces are the number one cause of rows that look like duplicates but never get flagged.

Can Google Sheets pull data from a website without code?
Yes, with IMPORTXML. For example, =IMPORTXML(A1, "//meta[@name='description']/@content") extracts the meta description of any URL directly into your sheet. Always wrap it in IFERROR: import formulas are volatile, and one failed call can cascade errors through your file.

Which formulas should I run before importing leads into a CRM?
Clean first: TRIM(CLEAN()) for invisible characters, PROPER for name casing, LOWER for emails, and SUBSTITUTE to strip legal suffixes like "Inc." from company names. Then validate: ISEMAIL or REGEXMATCH on the email column and COUNTIF to flag duplicates. Catching bad data in the sheet is far cheaper than fixing it inside the CRM.

Are formulas enough to fully enrich B2B data in Google Sheets?
They cover roughly 90% of cleanup, validation, and basic enrichment: VLOOKUP matches firmographics from a reference sheet, and SPLIT extracts the company domain from an email. What formulas cannot do is find data you do not have yet, like a prospect's verified email or phone number. For that step, an enrichment add-on like Derrick fills the missing columns directly inside the sheet.

Continue exploring this cluster

Google Sheets Enrichment

RegexMatch on Google Sheets: Guide 2026

REGEXMATCH for B2B data cleaning - patterns for emails, phones, URLs, with 12 ready-to-paste examples.

Read the guide → Google Sheets Enrichment

How to Build a Data Enrichment Dashboard in Google Sheets (Step-by-Step)

Build a live enrichment dashboard in Sheets - KPIs, charts, segment views. No BI tool needed.

Read the guide → Google Sheets Enrichment

How to Collaborate as a Team on Your B2B Data Enrichment Google Sheet

Multi-user Sheets workflows for sales ops - permissions, named ranges, change tracking, conflict resolution.

Read the guide → Google Sheets Enrichment

How to Export Google Sheets Data to Your CRM (Complete 2026 Guide)

Sheets → HubSpot/Salesforce/Pipedrive - 4 methods (native, Zapier, Make, custom) compared.

Read the guide → Google Sheets Enrichment

How to Automate B2B Data Enrichment with Zapier, Make and n8n

3 no-code platforms compared for Sheets enrichment automation - pricing, complexity, when each wins.

Read the guide → Google Sheets Enrichment

How to Install Derrick on Google Sheets (Step-by-Step 2026)

Install Derrick on Sheets in 30 seconds. Free tier, no credit card, first enrichment in 2 minutes.

Read the guide →

Start enriching your sheet in 30 seconds

Free for 100 credits/month. No credit card.

Install Derrick free →