Function Problems

Build a Formatted Email Address from Excel Columns

Anum MushtaqAnum Mushtaq Nov 18, 2026 868 views

Question details

Create consistently formatted email addresses by combining name, month, and year columns.

Build a Formatted Email Address from Excel 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.
Before you start

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.

Solution 1Recommended

Combine worksheet columns with an Excel formula

Use this formula for a regular worksheet range, then fill it down for the remaining rows.

1
Select the output cell

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.

2
Enter the formula

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.

3
Fill the formula down

Select the formula cell and drag its fill handle down the email column to apply it to the other rows.

Combine worksheet columns with an Excel formula
Year formatting: The format "00" adds a leading zero to single-digit numbers but does not shorten a four-digit year. To use the last two digits of a numeric year, replace TEXT(D8,"00") with TEXT(MOD(D8,100),"00").
Create email addresses in WPS Spreadsheets

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. 1. Open your worksheet: Open WPS Spreadsheets and select File > Open to choose your workbook.
  2. 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. 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. 4. Fill the column: Select the formula cell and drag its fill handle down to generate addresses for the remaining rows.
Generate email addresses from separate worksheet columnsFormat single-digit months and years with a leading zeroOpen and save Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface
microsoft office alternative - wps office

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.