The 15 Google Sheets Formulas to Enrich Your B2B Data

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.