Use Google Sheets as an Automated Email Database
Find all email addresses on a website and save them to Google Sheets
You can use the Add to Sheets extension to find all email addresses on a website and save them to Google Sheets. This is useful for building an email list or creating a database of contacts for your business or personal use.
The video below steps through the content of the post below:
How to find email addresses on a website
- Open the website you want to extract email addresses from in your browser.
- Open the Add to Sheets extension by clicking on the extension icon in your browser toolbar.
- Click on the Find Matches button in the extension side panel.
- Select the Emails option from the dropdown menu.
- Select where you want to save the email addresses in Google Sheets (you’ll need to configure the columns first if you haven’t already).
- Click the Save button to add the list of email addresses to Google Sheets.
Extracting domain names from email addresses
You can extract the domain names from email addresses using the =SPLIT()
function in Google Sheets. This can be useful for categorizing or analyzing email addresses based on their domain names.
To extract the domain names from email addresses, follow these steps:
- In a new column, enter the formula
=IF(ISBLANK(A2), "", SPLIT(A2, "@"))
(assuming the email addresses are in column A).
This formula will split the email addresses at the @
symbol and return an array (i.e. a list) with two elements: the email alias and the domain name.
For example if you have the emails addresses in column A:
Email Address (A) | Email Alias (B) | Email Domain (C) |
---|---|---|
[email protected] |
=IF(ISBLANK(A2), "", SPLIT(A2, "@")) |
This will return john
in cell B2 and example.com
in cell C2.
Email Address (A) | Email Alias (B) | Email Domain (C) |
---|---|---|
[email protected] |
john | example.com |
Using VLOOKUP to match email addresses with existing data
You can use the VLOOKUP
function in Google Sheets to match against the domain names extracted from email addresses with existing data in your sheet. This can be useful if you need to categorize or analyze email addresses based on other data in your sheet or another sheet in the workbook.
To use VLOOKUP
to match email addresses with existing data, follow these steps:
-
Create a new columns to store the aliases and domain names extracted from email addresses using the
=SPLIT()
function from above. -
Use the
VLOOKUP
function to match the domain names with existing data in your sheet. For example, you can use this VLOOKUP formula=IF(ISBLANK(C2), "", IFERROR(VLOOKUP(C2, 'Sheet2'!A:B, 2, FALSE), "Not found"))
to match the domain name in cell B2 with data in columns A and B of Sheet2. -
This will return the corresponding data from the second column of Sheet2 if a match is found.
In Sheet1, you’ll have the email addresses saved from the website and the domain names extracted from them. The additional column (Matched Data (D)) will be used to display the matched data from Sheet2 based on the domain name using the VLOOKUP
function.
Email Address (A) | Email Alias (B) | Email Domain (C) | Matched Data (D) |
---|---|---|---|
[email protected] |
test | gmail.com | =IF(ISBLANK(C2), "", IFERROR(VLOOKUP(C2, 'Sheet2'!A:B, 2, FALSE), "Not found")) |
[email protected] |
test | yahoo.com | =IF(ISBLANK(C3), "", IFERROR(VLOOKUP(C3, 'Sheet2'!A:B, 2, FALSE), "Not found")) |
[email protected] |
test1 | gmail.com | =IF(ISBLANK(C4), "", IFERROR(VLOOKUP(C4, 'Sheet2'!A:B, 2, FALSE), "Not found")) |
[email protected] |
test | outlook.com | =IF(ISBLANK(C5), "", IFERROR(VLOOKUP(C5, 'Sheet2'!A:B, 2, FALSE), "Not found")) |
In Sheet2, you’ll be matching against the domain names in column A and returning the corresponding data from column B:
Domain Name (A) | Data (B) |
---|---|
gmail.com | Example data for gmail.com |
yahoo.com | Example data for yahoo.com |
outlook.com | Example data for outlook.com |
The result (matched data) will be displayed in column C of Sheet1:
Email Address (A) | Domain Name (C) | Matched Data (D) |
---|---|---|
[email protected] |
gmail.com | Example data for gmail.com |
[email protected] |
yahoo.com | Example data for yahoo.com |
[email protected] |
gmail.com | Example data for gmail.com |
[email protected] |
outlook.com | Example data for outlook.com |
Column B is not shown above
Add individual items to your database
You can also add individual email addresses to your database by right-clicking on the email address and selecting the Add to Sheets option from the context menu. This will save the email address directly to Google Sheets without having to manually copy and paste it.
Conclusion
Using Google Sheets as an email database can help you organize and analyze email addresses for various purposes, like building an email list, categorizing contacts, or tracking email activity. The Add to Sheets extension makes it easy to extract email addresses from websites and save them right to Google Sheets, helping you to automate your entire process.
Try out Add to Sheets for free and start saving email addresses directly to Google Sheets from the web with just a right click.
If you have any questions or feedback, feel free to contact us or reach out on Twitter/X. We’d love to hear from you!
#tip #featured #how-to #chrome-extension #extract-emails #email-database #email-marketing #data-analysis #vlookup #marketing-automation #database