Combine first name and last name in Excel – create email addresses for example
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
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.
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!
You might also enjoy these articles
This is a tutorial on how to round time in Excel. This can feel a bit tricky if you are not familiar with how Excel handles time formatted data. Now we can’t just go ahead and use the ROUND function which might feel like the obvious choice. We need a different set of tools for this, because for Excel, data formatted as time behaves in a different way.