Build a Formatted Email Address from Excel Columns
Question details
Create consistently formatted email addresses by combining name, month, and year columns.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains separate columns for first name, surname, month, and year, and the user wants to generate email addresses from them.
- Observed behavior
- The desired result combines the surname, a period, the first name, formatted month and year values, and an email domain.
Check which columns contain the first name, surname, month, and year, and confirm that the month and year cells contain numbers rather than full dates. Choose the email domain you want to append.
Combine worksheet columns with an Excel formula
Use this formula for a regular worksheet range, then fill it down for the remaining rows.
Click an empty cell in row 8 where the email address should appear. This example assumes A8 contains the first name, B8 the surname, C8 the month, and D8 the year.
Type =B8&"."&A8&TEXT(C8,"00")&TEXT(D8,"00")&"@gmail.com" and press Enter. Change the cell references or domain if your worksheet uses different columns or an email provider other than Gmail.
Select the formula cell and drag its fill handle down the email column to apply it to the other rows.

Use structured references in an Excel Table
Use table column names to create a formula that can automatically populate the email column.
Combine Email Address Columns in WPS Spreadsheets
Use a formula in WPS Spreadsheets to combine names, formatted numbers, and a domain, then fill it down your worksheet.
- 1. Open your worksheet: Open WPS Spreadsheets and select File > Open to choose your workbook.
- 2. Select an output cell: Click an empty cell in the row containing the first name, surname, month, and year you want to combine.
- 3. Enter the formula: For data in A8 through D8, enter =B8&"."&A8&TEXT(C8,"00")&TEXT(D8,"00")&"@gmail.com" and press Enter. Adjust the references and domain to match your sheet.
- 4. Fill the column: Select the formula cell and drag its fill handle down to generate addresses for the remaining rows.

Frequently Asked Questions
How do I change the email domain?
In the formula, replace "@gmail.com" with your preferred domain, such as "@example.com", keeping the quotation marks.
How can I use only the last two digits of a four-digit year?
Replace TEXT(D8,"00") with TEXT(MOD(D8,100),"00"). For example, a numeric year of 2024 becomes 24.
Why does the formula show a four-digit year?
TEXT(D8,"00") specifies a minimum of two digits; it does not truncate longer numbers. Use TEXT(MOD(D8,100),"00") if you need the last two digits.
Does the formula create an email account?
No. The formula generates an address as text in the worksheet. An account must be created separately with the email provider.




