Small Business Stack

Normalize CRM Email Addresses Before a CSV Import

How do I normalize email addresses in Google Sheets before a CRM CSV import while preserving dots and plus tags?

Use =IF(A2="","",LOWER(TRIM(A2))) in a separate column to lowercase addresses and trim ordinary outer spaces without changing dots or plus tags. Check duplicates, blanks, and internal spaces, then paste approved results as values into an export tab and download it as a CSV.

=IF(A2="","",LOWER(TRIM(A2)))

What you need

  • Google Sheets with edit access to the working spreadsheet
  • Source email data with existing CRM record IDs
  • A separate column for normalized email results

Before you load a list of contacts into your CRM, put every email address into the same plain form: all lowercase, with no stray spaces at either end. That step is called normalizing. In Google Sheets, =IF(A2="","",LOWER(TRIM(A2))) does it in a new column and leaves your original addresses alone. This guide explains what that means and why it matters, then walks through it one click at a time.

What does normalizing an email address mean?

Normalizing means rewriting values that mean the same thing so they are also written the same way. For email addresses, that usually means two small changes: make every letter lowercase, and remove spaces someone typed by accident before or after the address.

A person reads Avery@Example.com and avery@example.com (with a space on the end) as the same address. A spreadsheet or a CRM compares text character by character, so to them those are two different values. Capital letters count as different characters, and so does a space you can’t see.

That matters because of how imports work. A CRM (customer relationship management system, such as HubSpot or Pipedrive) is the database where your business keeps its contacts. When you import a file, many CRMs use the email address to decide whether each row is a new person or someone already in the system. If the same person appears as Avery@Example.com in one place and avery@example.com in another, the CRM may not recognize them, and you end up with two records for one customer. Notes, deals and follow-ups get split between them.

A few more terms used below:

  • CSV stands for comma-separated values. It is a plain text file of rows and columns, and it’s the format most CRMs accept for imports. Google Sheets can save any tab as one.
  • Helper column is an extra column you add to hold a formula’s results, so the original data stays untouched next to it.
  • Plus tag is the part after a + in an address such as avery+newsletter@example.com. Many email services deliver it to the same inbox as avery@example.com, but it is still written differently, and this guide keeps it exactly as it is.

Normalizing only tidies how an address is written. It does not check that the address is real, that it receives mail, or that two records belong to the same person.

Which Google Sheets formula normalizes CRM emails without removing dots or plus tags?

This is the formula. You type it once and copy it down the column:

=IF(A2="","",LOWER(TRIM(A2)))

Read it from the inside out:

  • A2 is the cell holding the original address, column A, row 2.
  • TRIM removes spaces from the start and end of the text. If there are several spaces in a row inside the text, it cuts them down to one.
  • LOWER turns every capital letter into a small one.
  • IF(A2="","", ...) says: if A2 is empty, leave this cell empty too; otherwise, run the cleanup. Without it, empty rows would still be processed, which can confuse later checks.

The formula changes capital letters and spaces only. Dots and plus tags stay where they are:

  • Person@Example.com becomes person@example.com.
  • Person+Updates@Example.com becomes person+updates@example.com.
  • person.name@example.com stays dotted. It does not become personname@example.com.
  • person+updates@example.com keeps its tag. It does not become person@example.com.

Some email providers deliver dotted and tagged versions to the same inbox, but you can’t tell which providers do that from the address alone. Removing them would be guessing, so the formula doesn’t.

How do you set up source and normalized email columns in Google Sheets?

Keep the original addresses in one column and put the cleaned versions in the column next to them. That way you can always see what each result came from.

Here is the sample used in this guide. The addresses are made up. The double quotes only show where each cell starts and ends, so you can see the spaces; they are not part of the address. “Empty cell” means nothing is in it.

Sheet row A: Email_original B: Email_normalized C: Contact_ID
2 " Avery.Example@Example.com " DEMO-001
3 "AVERY.EXAMPLE@example.com" DEMO-002
4 "avery.example+Newsletter@Example.com " DEMO-003
5 "averyexample@Example.com" DEMO-004
6 Empty cell DEMO-005
7 " Morgan.Placeholder+West@Example.net" DEMO-006
8 "morgan placeholder@example.net" DEMO-007

Contact_ID is the record number your CRM already uses for each person. Keep it in the sheet so every row stays tied to the right contact.

  1. Make a copy to work on. Right-click the tab name at the bottom of the sheet and choose Duplicate. Work in the copy so the original export is never changed.
  2. Check where the addresses are. In this example they are in column A, with the heading Email_original in cell A1. If yours are in another column, use that column’s letter wherever this guide says A.
  3. Add the helper column. Type Email_normalized in B1. If column B already holds data, right-click the column B header and choose Insert 1 column left first.
  4. Type the formula in B2. Click cell B2, paste the formula, and press Enter. For the sample, B2 should now show avery.example@example.com.
  5. Copy the formula down. Click B2, then drag the small blue square at its bottom-right corner down to the last row of your data. Sheets adjusts the row number for each line, so B3 uses A3, B4 uses A4, and so on. For the sample, that is B2 to B8.
  6. Spot-check a few rows. Click B3 and look at the formula bar above the grid. It should say A3, not A2. If every row shows the same address, the reference was locked with dollar signs, as in $A$2. Remove them and copy the formula down again.
  7. Look over the whole column. Watch for empty results and addresses that still contain a space in the middle.

If you sort the data, select every column first, not just column B. Sorting one column on its own moves those cells but leaves the rest behind, so addresses end up next to the wrong contact IDs. For a wider cleanup of a contact list, see how to clean a contact spreadsheet for a HubSpot CSV import.

What results should the sample email normalization produce?

After copying the formula from B2 to B8, column B should match this table. It shows one likely duplicate and one address that needs a closer look.

Contact_ID Email_normalized Review result
DEMO-001 avery.example@example.com Outer spaces removed; letters lowercased
DEMO-002 avery.example@example.com Same normalized address as DEMO-001
DEMO-003 avery.example+newsletter@example.com Dot and plus tag preserved
DEMO-004 averyexample@example.com Still distinct from the dotted address
DEMO-005 Empty result No address supplied
DEMO-006 morgan.placeholder+west@example.net Plus tag preserved
DEMO-007 morgan placeholder@example.net Internal space remains; needs review

What each row tells you:

  • DEMO-001 and DEMO-002 looked different before (spaces, capitals) and are now identical. That is exactly what normalizing is for: it shows you a possible duplicate. It does not prove they are the same person, so check before you merge or delete anything.
  • DEMO-003 keeps +newsletter, so it does not match the pair above.
  • DEMO-004 has no dot, so it stays separate from avery.example.
  • DEMO-005 had no address and stays blank. Don’t fill it with a made-up value.
  • DEMO-007 still has a space in the middle. Real email addresses almost never contain one, so this row is probably a typo and needs checking.

How should you review duplicate emails, blanks, and remaining spaces?

Treat matches as questions to answer, not as records to delete. Before merging anything, compare the original addresses, the contact IDs, and the other details on each row.

Finding repeats. Select all your data, then choose Data > Sort range and sort by the Email_normalized column. Matching addresses now sit next to each other. Two rows with the same address may still have different names, owners or CRM IDs, so read them side by side. Your CRM’s import help pages explain how it matches incoming rows to existing contacts; check that before you import.

Blank addresses. Leave them blank. If your CRM requires an email for every contact, set those rows aside until someone finds the real address.

The formula shows as text instead of a result. The column may be formatted as plain text, or the formula may start with an apostrophe. Select column B, choose Format > Number > Automatic, delete any apostrophe at the start, and type the formula in B2 again.

Sheets says there is a formula parse error. Two common causes: curly quotation marks copied from a document (retype them as straight " marks), or a spreadsheet region that uses semicolons instead of commas between parts of a formula. In that case use =IF(A2="";"";LOWER(TRIM(A2))).

Spaces are still showing at the ends. Some systems export a lookalike character called a non-breaking space, which TRIM does not remove. Use this version instead, which swaps that character (code 160) for an ordinary space first:

=IF(A2="","",LOWER(TRIM(SUBSTITUTE(A2,CHAR(160)," "))))

A space in the middle of an address. Don’t just delete it. Check the address against an email from that person or another trusted record, then type the correct one into column A.

How do you export normalized emails as a values-only CRM CSV?

The cleaned addresses in column B are formulas. If you copy them into another tab as they are, they will try to point at cells that aren’t there. Paste them as values instead, meaning the text itself, with the formula removed.

  1. Add an export tab. Click the + at the bottom left of the sheet to create a new tab.
  2. Copy the columns your CRM needs. Select them in the working tab and press Ctrl+C (Cmd+C on a Mac).
  3. Paste as values. In the export tab, right-click cell A1 and choose Paste special > Values only.
  4. Rename the email heading. Change Email_normalized to the field name your CRM expects, usually Email. Leave out Email_original, so nobody maps the uncleaned column by mistake.
  5. Download the CSV. With the export tab open, choose File > Download > Comma-separated values (.csv). Sheets saves only the tab you are looking at.

Open the downloaded file in a text editor, such as Notepad or TextEdit, to check the email heading and confirm the plus tags are still there. During the import, read your CRM’s preview screen carefully. It shows which column goes into which field, and whether rows will create new contacts or update existing ones. If your notes field has commas or line breaks, also see how to export CRM notes to CSV without breaking them. More guides on the software small teams run on are in the small business stack.

When should you avoid this Google Sheets email-normalization method?

This formula is for a one-off tidy-up before an import. It’s the wrong tool in a few cases:

Requirement Appropriate alternative
Preserve mailbox capitalization Keep original addresses and apply only approved transformations, such as normalizing the domain separately.
Confirm mailbox existence or deliverability Use a validation process appropriate to the destination; exclude uncertain records.
Apply identical rules across recurring imports Use a controlled import workflow with explicit rejected-row handling and preserved source values.

The domain is the part after the @. The part before it is, by the rules, allowed to be case-sensitive, though most email services ignore case. If a system you send to does treat capitals as different, lowercasing could break those addresses.

Also remember what normalizing can’t do. An address can be neat and lowercase and still have a typo, belong to someone who left the company, or bounce. Keep the original column until the import is finished and checked.

Sources

Esc