Clean a Contact Spreadsheet for a HubSpot CSV Import
How do I clean a contact spreadsheet in Google Sheets for a HubSpot CSV import?
Keep source records separate from cleanup formulas. Normalize emails with =LOWER(TRIM(Raw!B2)), then review repeated and missing addresses. Export approved rows with First Name, Last Name, Email Address, and Company Name. Before importing, check HubSpot's property mapping and how it will update existing contacts.
To clean the contact spreadsheet, keep the source intact, normalize the email text, and review repeated or missing email keys before exporting a CSV. Then map the four approved export columns to contact properties following HubSpot’s import-file requirements. Tidier formatting is not, by itself, a reason to update every matching contact.
How should you set up the contact cleanup spreadsheet?
Keep source records in Raw, cleanup formulas in Clean, and approved import rows in Export.
This workflow uses email as its matching key, so leave out contacts without an email address. You can use other identifiers supported by HubSpot, but those need a different review plan.
- Make a separate copy of the original file and leave it untouched.
- Enter Full Name, Email Address, Company Name in
Raw!A1:C1. - Give each contact its own source row, and remove report titles, merged cells, and subtotals.
- Enter First Name, Last Name, Email Address, Company Name, Duplicate Email in
Clean!A1:E1. - Add the formulas below to row 2, then fill down to the last source record.
- Copy only approved cleaned values into
Export; leave out source rows still awaiting review.
Do not rearrange these columns. The duplicate formula checks column C, and the sample’s source records stop at row 6.
Names and companies in the sample are fictional. The backticks show exact cell content, spaces included, but are not part of what you enter. Keep the last email cell 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 prepare the contact columns?
The formulas below split names at the first space, normalize emails, trim company names, and flag email values that appear more than once.
Put each formula in the cell specified for it. Do not paste the whole block into a cell. These formulas use US syntax with comma separators; map their output columns to the contact properties described in HubSpot’s import guidance.
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
According to Google Sheets’ TRIM documentation, the function removes surrounding spaces and reduces repeated ordinary spaces within text. Here, it strips the padding from the sample names before the split. It cannot tell you where a person’s given name ends.
These name formulas follow the first space, not naming conventions. That is why the output is Fizzle and Von Moon. The split obeys a text rule without establishing whether those are the person’s given and family names.
The duplicate formula uses COUNTIF to count matching values in $C$2:$C. Blank email cells get no flag. Every row in a repeated group gets one, leaving the choice of which record to keep to you.
What should the cleaned sample look like?
Expect two flagged Zibble rows, a single-word name with an empty last name, and a contact without an email in the cleaned sample.
The notation (blank) means the cell is empty; do not type it into the sheet. Before deleting records, compare every cleaned row against its source.
| 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) |
Retain one of the identical Zibble records and put Twidget on a separate review tab. Of the five source records, one Zibble row is redundant and one row must be held for missing email. That leaves 5 - 1 - 1 = 3 export records.
Two duplicate flags identify one redundant row in this sample. Deleting both flagged rows would remove too much. Blorpo stays under this email-keyed review rule because an empty Last Name does not disqualify the record.
The approved rows produce the following CSV:
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
Leave the held record available for correction. Making up an address would replace an obvious missing value with a matching key that has no basis in the source.
How should you resolve duplicate emails before importing?
Compare the contact details on every row sharing an email address before approving a single final record.
I prefer checking the whole duplicate group rather than automatically keeping its first row. Source order tells you nothing about accuracy. Compare names and companies, and retain only values supported by the source. The same email address can appear on records for different people.
Keep records for different people sharing a mailbox out of this import. Combining them into one contact would clear the duplicate flag while hiding the unresolved question of how those people should be recorded.
The COUNTIF check looks only at the spreadsheet range you give it. It does not query the CRM. Even an email that appears just once in the CSV can match an existing contact under HubSpot’s identifier rules.
Record the reasons for duplicate decisions somewhere outside the four-column export. If approved source values change, rebuild the export and review it again. The new data may also require you to revisit the earlier decision.
How do you fix name errors and invisible email spaces?
Restore the complete name formulas to fix name errors, and check for invisible characters when emails look identical but fail to match.
With Blorpo, the expected output is First Name Blorpo and an empty Last Name. A name in both columns, or an error in either, calls for a character-by-character comparison of both name formulas against the supplied block. The full name must not be the fallback for both columns.
Some imported characters survive ordinary trimming. Google’s TRIM documentation describes its limits with nonbreaking spaces. To handle that case, use this formula in Clean!C2 and fill it down:
=LOWER(TRIM(SUBSTITUTE(CLEAN(Raw!B2),CHAR(160)," ")))
After replacing the formula, inspect the affected addresses and duplicate flags again. A visible internal space, as in zibble @example.com, needs a different check: confirm the intended address before changing the source. I would not delete internal spaces from all contact emails with a blanket rule.
If a formula fails as soon as you enter it, check its separators and straight quotation marks against the supplied US-syntax version. Use a separate copy if necessary rather than changing a shared operational spreadsheet’s locale just for this cleanup.
How do you prepare the CSV and map HubSpot properties?
Copy approved rows as values into Export, then map each column to the intended HubSpot contact property.
Delete Duplicate Email, held records, and unused rows from the export itself. Hiding or filtering rows is not a dependable way to leave them out. Before uploading the CSV, compare its headers and record count with the three-record sample.
Check the following column-to-property mappings in the HubSpot import:
- Send First Name to the First name property.
- Send Last Name to the Last name property.
- Send Email Address to the Email property.
- Send Company Name to the Company name property.
Limit this file to the four planned fields. If you want to add free-text notes, first follow the guide to exporting CRM notes to CSV without breaking commas or line breaks. Only expand the import after that review.
Company name supplies a text value on the contact. Mapping it does not tell HubSpot to create a company record or associate one with the contact. Since HubSpot handles associations separately during import, plan those relationships apart from this property mapping.
Also check how the import will update existing contacts. Empty CSV cells are not a dependable way to clear properties. HubSpot’s import guidance distinguishes a blank imported value from an instruction to remove data already in the CRM.
When should you avoid name splitting or email-only matching?
Skip first-space splitting if reliable separate name fields are available, and reconsider email matching for updates to known records whose addresses have changed.
Keep structured First Name and Last Name values as they are; joining them only to split them again adds no useful information. When a given name contains multiple words, ask for structured fields or review it manually. The formula cannot recover naming context that the source does not provide.
For controlled updates to known contacts, HubSpot Record IDs may be a better choice than uncertain email matches. Using them requires a different file design, not simply another cleanup formula.
Once you have decided which contacts belong in the import, use Flag CRM Contacts Missing an Owner or Next Step to review operational gaps. I would leave those assignments outside this four-column cleanup so that correcting a contact’s identity does not accidentally assign ownership.
Alongside the other small-business stack guides, this workflow prepares approved contact identities and their mapped values. Keep company associations and follow-up assignments outside this contact file.