In the example shown, the formula in D5 is: First, the LEFT function is used to get the first letter from the first name in column C. Usually, the LEFT function gets a “num_chars” value for the second argument, but the argument is optional and defaults to 1 if omitted. The first letter of the first name is then joined to the last name using the concatenation operator (&), and the result is wrapped in the LOWER function, which forces all text to lower case. Finally, result is jointed again to “@” and then to the domain. The domain is kept separate for convenience only. The formula could be written like this:

With a named range

To simply the formula, you could create a named range “domain” to hold the domain name, then rewrite the formula like this: Changing the value in the named range will then update all email addresses at once.

Dave Bruns

Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.