Combine first name and last name in Excel – create email addresses for example

By: Ilkka Mäkinen | Published: 8 April, 2016 | In: Excel

concatenate-excel-combine-text-create-emails

The end result of this post: we have created email addresses to column D from the data in columns A, B and C.

This is a quick tutorial on how to combine first name and last name in Excel. You can use it to create email addresses from a list of names for example. To combine strings of text or values in cells we can use the CONCATENATE function.

This is how it looks like:

=CONCATENATE(text one; text two; text three...)

Example

concatenate-excel-combine-text-create-emails

To create email addresses from the names and the company domain shown in the image above we would create the following formula to cell D2:

=CONCATENATE(A2;".";B2;"@";C2")

Note: depending on your regional settings, you might have to use comma (,) as a separator instead of semicolon (;):

=CONCATENATE(A2,".",B2,"@";D2)

Why the quotes in the formula?

It's because Excel wouldn't understand what to do with the dot or the @ symbol and give me an error otherwise. When you insert string of text to a function or a formula, you have to use quotes. Always. This is how we tell Excel that this here is a string of text, or just one symbol. It would not understand this otherwise.

Note, if you have the text you want in a cell somewhere, don't put the cell name in brackets or Excel will not use the value in that cell. Notice above how we did not include the cell names in brackets (A2, B2, C2...)

But yes, there you have it, end result for this formula will be John.Allister@example.com.

 

Subscribe to our newsletter

Get the latest expert advice from our blog on Excel, WordPress, search engine optimization, Google analytics and Adwords straight to your email.

 

But I want to change it all to lowercase

Yes we got the email address allright but it's a bit ugly with those capital letters wasn't it? Lucky us, it's very easy to change the characters to lowercase. Any string of text can be changed to lowercase with the function LOWER, just wrap the formula we just made before inside the LOWER function:

=LOWER(CONCATENATE(A2;".";B2;"@";C2))

And now you have the email in lowercase:  john.allister@example.com.

That's it for this tutorial, in the next post we will be looking at how to replace text and characters from any text.

You can learn more tips and the efficient use of Excel in our Excel courses. The Excel Essentials training course is perfect for for beginners and intermediate levels, the Excel Advanced course is the best match for those who want to become more advanced Excel users.

Until next time, cheers!

By: Ilkka Mäkinen | Published: 8 April, 2016 | In: Excel

Want to stop messing about and learn to use Excel efficiently?

Save your spot in our Excel Essentials course and learn how to make Excel rock!

Terms & Conditions

Conditions: Minimum number of participants is 3 persons on each course. If the course you are enrolling in does not reach the minimum number of participants it will be cancelled or postponed. We will inform enrolled participants in case there are not enough participants on a course.

Cancellations and substitutes: a) Cancellations confirmed more than 21 days prior to the date of the course are fully refundable and free of charge. Cancellations are to be made in writing to info@talentwire.eu. 

b) Cancellations made within 21 days of the date of the course may not be cancelled without incurring course charges fully.

c) Substitutes may be made at any time free of charge. TalentWire should be informed of any changes.

Pricing and invoicing: Payment for each course is to be received no later than 14 days prior to the course start date. We will send an invoice after your booking has been confirmed. Inform us of any special arrangements you require for your invoices.

All prices are as stated and are exclusive of VAT which will where appropriate be charged at the current rate.

×