Spreadsheet Text Functions for Email Data: TEXTJOIN, SUBSTITUTE, MID, FIND, LEFT, RIGHT and More for Cleaning Email Lists
On this page
Why Text Functions Matter for Email Data
Email data rarely arrives in perfect condition. Exported lists contain extra spaces, mixed case, merged name-and-email fields, inconsistent formatting and encoding issues. Spreadsheet text functions let you clean, parse and transform email data without leaving Excel or Google Sheets:
| Problem | Text function solution |
|---|---|
| " user@example.com " (extra spaces) | TRIM(A1) |
| "User@Example.COM" (mixed case) | LOWER(A1) |
| "John Smith user@example.com" (name + email merged) | MID + FIND to extract email |
| Need domain from email | MID(A1, FIND("@", A1)+1, LEN(A1)) |
| Need username from email | LEFT(A1, FIND("@", A1)-1) |
| "first.last@example.com" needs "First Last" | PROPER + SUBSTITUTE |
| Build email from first name + last name + domain | LOWER(B1) & "." & LOWER(C1) & "@" & D1 |
| Remove invalid characters | SUBSTITUTE chains |
| Split "user@example.com; user2@example.com" | TEXTSPLIT (Excel 365) or manual formulas |
Core Text Functions Reference
Functions that work in both Excel and Google Sheets
| Function | Syntax | What it does | Email use case |
|---|---|---|---|
| LOWER | LOWER(text) | Converts text to lowercase | Standardise email case |
| UPPER | UPPER(text) | Converts text to uppercase | N/A (emails should be lowercase) |
| PROPER | PROPER(text) | Capitalises first letter of each word | Format names extracted from emails |
| TRIM | TRIM(text) | Removes leading, trailing and extra internal spaces | Clean imported email data |
| LEN | LEN(text) | Returns character count | Validate email length (flag very short or very long) |
| LEFT | LEFT(text, num_chars) | Returns leftmost characters | Extract username portion |
| RIGHT | RIGHT(text, num_chars) | Returns rightmost characters | Extract TLD |
| MID | MID(text, start_num, num_chars) | Returns characters from middle | Extract domain; extract portions |
| FIND | FIND(find_text, within_text) | Returns position of text (case-sensitive) | Find @ position; find "." position |
| SEARCH | SEARCH(find_text, within_text) | Returns position of text (case-insensitive) | Find text regardless of case |
| SUBSTITUTE | SUBSTITUTE(text, old, new) | Replaces specific text | Remove unwanted characters; fix typos |
| REPLACE | REPLACE(text, start, num_chars, new) | Replaces by position | Replace characters at known positions |
| CONCATENATE or & | A1 & B1 or CONCATENATE(A1, B1) | Joins text | Build email from components |
| EXACT | EXACT(text1, text2) | Case-sensitive comparison | Compare emails exactly |
| REPT | REPT(text, number) | Repeats text | N/A (rarely used for email) |
| CLEAN | CLEAN(text) | Removes non-printable characters | Clean imported data with encoding issues |
| VALUE | VALUE(text) | Converts text to number | N/A (rarely used for email) |
| TEXT | TEXT(value, format) | Formats number as text | Format dates in email-related data |
Functions available in Excel 365 and Google Sheets (newer)
| Function | Syntax | What it does | Email use case |
|---|---|---|---|
| TEXTJOIN | TEXTJOIN(delimiter, ignore_empty, text1, ...) | Joins text with delimiter | Combine multiple email columns with ";" separator |
| TEXTSPLIT | TEXTSPLIT(text, col_delimiter) (Excel 365) | Splits text by delimiter | Split "email1; email2" into separate cells |
| TEXTBEFORE | TEXTBEFORE(text, delimiter) (Excel 365) | Returns text before delimiter | Extract username: TEXTBEFORE(A1, "@") |
| TEXTAFTER | TEXTAFTER(text, delimiter) (Excel 365) | Returns text after delimiter | Extract domain: TEXTAFTER(A1, "@") |
Common Email Data Cleaning Formulas
Extract domain from email address
Standard formula (all versions):
=MID(A1, FIND("@", A1)+1, LEN(A1)-FIND("@", A1))
Excel 365:
=TEXTAFTER(A1, "@")
Extract username from email address
Standard formula:
=LEFT(A1, FIND("@", A1)-1)
Excel 365:
=TEXTBEFORE(A1, "@")
Extract name from "First Last <email>" format
Extract email:
=MID(A1, FIND("<", A1)+1, FIND(">", A1)-FIND("<", A1)-1)
Extract name:
=TRIM(LEFT(A1, FIND("<", A1)-1))
Build email from first name + last name + domain
=LOWER(B1) & "." & LOWER(C1) & "@" & D1
Standardise email to lowercase and trim
=LOWER(TRIM(CLEAN(A1)))
Fix common domain typos
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(TRIM(A1)), "@gmial.com", "@gmail.com"), "@gmai.com", "@gmail.com"), "@yaho.com", "@yahoo.com"), "@hotmal.com", "@hotmail.com")
Check if cell contains a valid-looking email
=AND(ISERROR(FIND(" ", TRIM(A1))), NOT(ISERROR(FIND("@", A1))), NOT(ISERROR(FIND(".", A1, FIND("@", A1)))), FIND("@", A1)>1, LEN(A1)-FIND("@", A1)>3)
This checks for: no spaces, contains @, contains "." after @, @ is not the first character, and domain is at least 4 characters (x.xx).
Extract TLD from email
=MID(A1, FIND("@", A1)+FIND(".", MID(A1, FIND("@", A1)+1, LEN(A1)))+1, LEN(A1))
Flag free email providers
=IF(OR(RIGHT(A1, 10)="@gmail.com", RIGHT(A1, 10)="@yahoo.com", RIGHT(A1, 12)="@hotmail.com", RIGHT(A1, 12)="@outlook.com"), "Free", "Business")
Count emails per domain
Use a helper column to extract domains, then:
=COUNTIF(DomainColumn, MID(A1, FIND("@", A1)+1, LEN(A1)))
Split semicolon-separated emails into rows
In Excel 365:
=TEXTSPLIT(A1, ";")
In older versions, this requires a helper column approach or VBA.
Combining Functions for Complex Transformations
Convert "lastname, firstname" to "firstname.lastname@domain.com"
=LOWER(TRIM(MID(A1, FIND(",", A1)+1, LEN(A1)))) & "." & LOWER(TRIM(LEFT(A1, FIND(",", A1)-1))) & "@" & B1
Extract all unique domains from a list
In Excel 365, combine UNIQUE with domain extraction:
=UNIQUE(MID(A1:A100, FIND("@", A1:A100)+1, LEN(A1:A100)-FIND("@", A1:A100)))
(Enter as a dynamic array formula in Excel 365.)
When Text Functions Are Not Enough
Spreadsheet text functions are effective for standardising format, extracting components and fixing known typos in email lists. However, they cannot verify whether an email address actually exists, check MX records, detect disposable email providers or validate deliverability.
For bulk extraction and deduplication of email addresses from files before applying spreadsheet cleaning, upload your source files to Email Extractor. The tool handles regex-based extraction from 19 file formats (including XLSX, CSV, PDF, DOCX and more) and deduplicates across all uploaded files, giving you a clean starting list to apply further text function transformations to in your spreadsheet.
