Normalize CRM Email Addresses Before a CSV Import

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

The Google Sheets formula =IF(A2="","",LOWER(TRIM(A2))) lowercases email text and trims ordinary outer spaces without removing dots or plus tags.

Enter it in a separate column, not over the source email:

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

If A2 is empty, the formula returns an empty string. Otherwise, TRIM removes ordinary leading and trailing spaces, and LOWER lowercases the text.

Google Sheets TRIM also reduces repeated ordinary internal spaces to one space. It does not remove every internal space or validate email addresses.

• Person@Example.com becomes person@example.com.
• Person+Updates@Example.com becomes person+updates@example.com.
• person.name@example.com does not become personname@example.com.
• person+updates@example.com does not become person@example.com.

Keep dotted and tagged strings distinct, even if one provider routes variants to the same inbox. Lowercasing the whole address is a deliberate matching policy: domains are case-insensitive, but mailbox names can technically be case-sensitive.

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

Create an unchanged source column and a separate normalized column so every transformed email remains traceable to its original value.

The sample uses fictional addresses. Double quotes show cell boundaries and are not part of the contents; “Empty cell” means genuinely blank.

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

1. Make a working copy of the source tab. Preserve the untouched source and include existing CRM record IDs.
2. Put original emails in column A. Set A1 to Email_original. If emails occupy another column, adjust the formula reference accordingly.
3. Create the output column. Set B1 to Email_normalized; leave column A unchanged.
4. Enter the formula in B2. The sample result should be avery.example@example.com.
5. Fill through the last contact row. Select B2:B8 for the sample, then choose Edit > Fill > Down. For larger files, continue through the actual last row, including rows after blank emails.
6. Check row references. Confirm that B3 refers to A3. Do not use $A$2, which would make every result read the same source cell.
7. Review the entire range. Check blanks and internal spaces. Sort the full contact range, never just the email column.

Sorting only column B can detach emails from their contact IDs.

What results should the sample email normalization produce?

Filling the Google Sheets normalization formula through B2:B8 should produce the following values, including one duplicate pair and one address requiring internal-space review.

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

DEMO-001 and DEMO-002 are potential duplicates, not automatic deletion candidates. DEMO-003 remains distinct because its plus tag survives; DEMO-004 remains distinct because dots are preserved. DEMO-007 shows why normalized text can still be unsuitable for import.

Why does IF with LOWER and TRIM normalize emails while preserving dots and plus tags?

IF with LOWER and TRIM normalizes emails while preserving dots and plus tags because it handles empty input, changes letter case, and adjusts ordinary spaces without replacing punctuation. First, IF evaluates A2="" to decide whether the source has an email value. When that comparison is true, it returns "", keeping missing email output empty rather than inserting a placeholder that could be mistaken for data.

For a nonempty value, TRIM(A2) removes ordinary outer spaces and reduces repeated ordinary internal spaces to one. This targets spacing rather than deleting all spaces or rewriting address characters. Next, LOWER converts the trimmed text to lowercase, applying the guide’s deliberate matching policy rather than retaining capitalization differences. Thus, DEMO-001 and DEMO-002 produce the same normalized string, while dots and plus tags remain part of the matching value.

Finally, placing the formula in B2 preserves the original in A2. Filling down updates the relative reference for each contact instead of repeatedly reading one fixed cell.

How should you handle duplicate emails and blank results before CRM import?

Review repeated nonblank normalized emails against original values, record IDs, and CRM matching rules rather than automatically merging or deleting contacts.

Sort the full dataset by Email_normalized to group repeated values. Identical emails can belong to records containing different information or different existing CRM IDs.

Do not create another matching key that strips dots or plus tags. Check the destination CRM’s behavior: preserving those characters in a CSV does not guarantee that the CRM treats the addresses as distinct. If its rules collapse differences, use an appropriate unique record ID or resolve the matching behavior before import.

Leave missing emails empty. If email is required, exclude those rows until verified addresses are available.

How do you fix formula errors and remaining spaces in Google Sheets?

Fix email-normalization failures by checking formula entry, locale separators, and whitespace characters before changing source addresses.

The formula appears as text

Symptom: B2 displays =IF(A2="","",LOWER(TRIM(A2))) literally.

Cause: Plain-text formatting or a leading apostrophe can prevent evaluation.

Exact fix: Select column B, choose Format > Number > Automatic, remove any leading apostrophe, then re-enter the formula in B2 and fill down. Changing the format alone may not reevaluate existing text.

B2 reports a formula parse error

Symptom: Google Sheets rejects the formula.

Cause: The sheet may require semicolon separators, or the formula may contain curly quotation marks.

Exact fix: Use straight double quotes. For semicolon-based locales, enter =IF(A2="";"";LOWER(TRIM(A2))).

Alternatively, set the workbook locale to United States under File > Settings and use the comma-separated formula. Change a shared workbook’s locale only when appropriate because other formatting can change.

Spaces remain around an address after TRIM

Symptom: Results still appear padded, or apparently identical addresses do not match.

Cause: Nonbreaking spaces are not removed by ordinary TRIM.

Exact fix: When inspection confirms nonbreaking spaces, replace B2 with =IF(A2="","",LOWER(TRIM(SUBSTITUTE(A2,CHAR(160)," ")))), then fill down.

This variation converts character 160 to an ordinary space before trimming. It does not remove every invisible character; inspect remaining mismatches rather than broadening cleanup blindly.

An internal space survives normalization

Symptom: DEMO-007 produces morgan placeholder@example.net.

Cause: TRIM collapses repeated internal spaces but does not join separated text.

Exact fix: Check the contact’s source of truth. Replace A8 with a verified address, or exclude the row until verification.

Do not automatically delete internal spaces. A dot or another character might belong in that position.

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

Copy approved rows into a dedicated export tab using Paste special > Values only, then download that active tab as CSV.

Values-only pasting freezes results so later formula edits do not change the export tab. Retain the working tab for comparison.

Include only intended import fields. Rename the normalized email header to the CRM’s expected header, and omit Email_original unless it has a specific separate destination. Two ambiguous email columns increase the risk of mapping the uncleaned field.

With the export tab active, choose File > Download > Comma-separated values (.csv). This option exports the current tab.

Inspect the downloaded CSV in a text editor to avoid spreadsheet formatting changes. Confirm the email header and preserved plus tags. In the CRM import preview, verify field mapping and whether the operation creates contacts or updates existing records.

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

Avoid this formula when mailbox capitalization must be preserved, address validity must be established, or recurring imports need controls beyond a manually filled helper column.

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.

A trimmed, lowercase string can still contain an invalid domain or incorrect mailbox name. Normalization cannot establish mailbox ownership, repair guessed addresses, or compensate for the wrong CRM import action.