Clean a Contact Spreadsheet for a HubSpot CSV Import

How should you set up Google Sheets for a HubSpot contact import?

Create Raw, Clean, and Export tabs in Google Sheets to separate contact source data, cleanup formulas, and the final HubSpot CSV.

This workflow uses email as its matching key. Hold contacts without email outside the import, even though HubSpot supports other identification methods.

1. Create Raw. Preserve an untouched original file separately. Set A1:C1 to Full Name, Email Address, Company Name.
2. Paste contacts below the headers. Keep one contact per row; remove merged cells, report titles, and subtotals.
3. Create Clean. Set A1:E1 to First Name, Last Name, Email Address, Company Name, Duplicate Email. Keep this order because duplicate detection references C.
4. Enter the formulas below in Clean row 2. Keep formulas out of the source columns.
5. Fill A2:E2 through the last source record. For this sample, fill through row 6. Confirm the final formulas reference the final populated Raw row.
6. Create Export after review. Copy the cleaned range using Edit > Paste special > Values only. Resolve flagged rows there, then remove Duplicate Email.

The sample names and companies are fictional; example.com is reserved for examples. Backticks mark actual cell content, including spaces; do not enter the backticks. Leave the final email empty.

Raw row	Full Name	Email Address	Company Name
2	Zibble Quasar	ZIBBLE@EXAMPLE.COM	Orbit Widget Co.
3	Fizzle Von Moon	Fizzle@Example.com	Moon Desk Studio
4	Blorpo	BLORPO@example.com	Solo Spark Shop
5	Zibble Quasar	zibble@example.com	Orbit Widget Co.
6	Twidget Comet	(blank)	Comet Paper Lab

Which formulas clean and map the HubSpot contact columns?

Use Google Sheets formulas to split names at the first space, lowercase and trim emails, trim company names, and flag repeated normalized emails.

Enter each formula in its specified cell, not the entire block together. These formulas use US syntax with comma separators.

SOURCE TAB: Raw
A1: Full Name
B1: Email Address
C1: Company Name

CLEANUP TAB: Clean
A1: First Name
B1: Last Name
C1: Email Address
D1: Company Name
E1: Duplicate Email

COLUMN MAPPING TABLE
| Export CSV column | Clean column | HubSpot contact property | Source |
|---|---|---|---|
| First Name | A | First name | Raw column A, before the first space |
| Last Name | B | Last name | Raw column A, after the first space |
| Email Address | C | Email | Raw column B |
| Company Name | D | Company name | Raw column C |

FORMULAS

Clean!A2 - First Name
=IFERROR(LEFT(TRIM(Raw!A2),FIND(" ",TRIM(Raw!A2))-1),TRIM(Raw!A2))

Clean!B2 - Last Name
=IFERROR(MID(TRIM(Raw!A2),FIND(" ",TRIM(Raw!A2))+1,LEN(TRIM(Raw!A2))),"")

Clean!C2 - Email Address
=LOWER(TRIM(Raw!B2))

Clean!D2 - Company Name
=TRIM(Raw!C2)

Clean!E2 - Duplicate Email
=IF(C2="","",IF(COUNTIF($C$2:$C,C2)>1,"DUPLICATE",""))

FILL-DOWN RANGE FOR THE SAMPLE
Copy Clean!A2:E2 into Clean!A3:E6.

EXPORT HEADERS
First Name,Last Name,Email Address,Company Name

DO NOT EXPORT
Duplicate Email

TRIM() removes ordinary surrounding spaces and collapses repeated ordinary internal spaces. IFERROR() keeps a single-word name in First Name and leaves Last Name empty.

LOWER(TRIM()) normalizes email text; it neither validates addresses nor repairs domain typos. Duplicate detection counts normalized values, excludes blanks, and keeps $C$2:$C anchored during fill-down.

What cleaned output should the sample contacts produce?

The sample Google Sheets formulas produce two flagged Zibble records, one single-word name with no last name, and one contact without email.

Here, (blank) represents an empty cell; formulas return empty strings, not that label.

First Name	Last Name	Email Address	Company Name	Duplicate Email
Zibble	Quasar	zibble@example.com	Orbit Widget Co.	DUPLICATE
Fizzle	Von Moon	fizzle@example.com	Moon Desk Studio	(blank)
Blorpo	(blank)	blorpo@example.com	Solo Spark Shop	(blank)
Zibble	Quasar	zibble@example.com	Orbit Widget Co.	DUPLICATE
Twidget	Comet	(blank)	Comet Paper Lab	(blank)

Both Zibble rows are intentionally flagged: the formula identifies every duplicate-group member. Keep one identical Zibble record in Export, move Twidget to a separate review tab, and remove Duplicate Email. Never invent a missing address.

The resulting CSV is:

First Name,Last Name,Email Address,Company Name
Zibble,Quasar,zibble@example.com,Orbit Widget Co.
Fizzle,Von Moon,fizzle@example.com,Moon Desk Studio
Blorpo,,blorpo@example.com,Solo Spark Shop

Blorpo’s missing Last Name can remain empty; a missing matching key cannot remain in this email-keyed import.

Why do TRIM, IFERROR, and COUNTIF work together for this HubSpot import?

TRIM, IFERROR, and COUNTIF turn source text into consistently mapped contact fields and identify repeated email keys for review. In column A, TRIM first standardizes ordinary spacing, then FIND locates the first space and LEFT extracts the first name. This applies a defined boundary rather than guessing which words belong together. IFERROR supplies the trimmed name as the fallback. In column B, the same boundary feeds MID, while LEN provides enough characters to capture the remaining text. That preserves Von Moon together rather than splitting every word into a separate column; its fallback is empty text.

Column C applies TRIM before LOWER, making email comparisons independent of ordinary surrounding spaces and capitalization. Column D trims company text without changing its case. Finally, column E uses IF to skip empty email values, then COUNTIF counts each address in the anchored $C$2:$C range. Checking the whole range rather than only earlier rows marks both Zibble records, so review covers the entire duplicate group. Filling down changes each source-row reference while keeping that comparison range fixed.

How should you resolve duplicate emails before importing into HubSpot?

Resolve duplicate emails by comparing each group’s contact details in the values-only Export tab before deleting redundant rows.

Matching addresses can accompany conflicting companies or differently completed names. Select verified final values rather than automatically keeping the first row. Preserve Raw; if source data changes, rebuild Export because pasted values do not refresh.

Hold shared mailboxes such as office@example.com aside when they represent different people. This email-keyed workflow cannot preserve separate identities without further decisions.

The duplicate formula checks only this spreadsheet, not HubSpot. A unique CSV address may match an existing CRM contact and cause an update.

How do you fix name errors, invisible email spaces, and formula parse failures?

Fix Google Sheets cleanup failures with the complete name formulas, additional email character cleanup, or corrected locale and quote settings.

A single-word name errors or appears in both name columns

Symptom: Blorpo returns #VALUE! or repeats in Last Name.

Exact fix: Replace Clean!A2 and Clean!B2 with the complete IFERROR() formulas above and fill down. The first-name fallback preserves the trimmed name; the last-name fallback returns "". Never use the full name as both fallbacks.

Identical-looking emails escape duplicate detection

Symptom: An invisible nonbreaking space prevents otherwise identical emails from matching.

Exact fix: Replace Clean!C2 and fill down:

=LOWER(TRIM(SUBSTITUTE(CLEAN(Raw!B2),CHAR(160)," ")))

This converts nonbreaking spaces into ordinary spaces and removes certain nonprinting characters; duplicate flags recalculate. For an internal space such as zibble @example.com, verify the address and edit Raw!B2. Do not automatically delete all internal spaces.

Formulas immediately show a parse error

Symptom: Google Sheets rejects formulas before producing values.

Exact fix: Open File > Settings, set Locale to United States, save, and re-enter the formulas. Use a separate working spreadsheet if changing locale would affect existing dates or numbers. Replace curly quotation marks with straight double quotes.

How do you export the cleaned CSV and map HubSpot properties?

Download the values-only Export tab as CSV and explicitly map its four columns to HubSpot contact properties.

Before downloading, remove blank rows, unresolved records, and Duplicate Email. Keep the four export headers shown above. Do not rely on filters to exclude records.

Select Export, then File > Download > Comma-separated values (.csv); Google Sheets downloads the current sheet. Confirm the file contains the intended headers rather than Full Name.

In HubSpot, select contacts as the import object, upload the CSV, and review every mapping:

• First Name: First name.
• Last Name: Last name.
• Email Address: Email.
• Company Name: Company name.

The contact property Company name is text, not an instruction to create or associate company records. Plan company associations separately.

Review existing-contact and property-update behavior. Blank CSV cells are not a dependable property-erasing method; use HubSpot’s supported clearing method instead.

When should you avoid first-space name splitting or email-only matching?

Avoid first-space splitting when structured names already exist or names require manual interpretation, and prefer Record IDs for controlled updates to known HubSpot records.

• Reliable separate name fields: Keep existing First Name and Last Name values rather than recombining and splitting them.
• Names containing multiple given-name words: Review manually or obtain structured fields from the original form. The mechanical rule makes Fizzle Von Moon into Fizzle and Von Moon; it cannot reliably infer naming conventions.
• Large updates or changed emails: Export HubSpot Record IDs and build a controlled update file using those identifiers instead of relying on uncertain email matches.