Formula Friday – Use Concatenate Or & To Create Usernames In Excel


If you need to create a username in Excel for an account or login, here’s a quick and easy way to do it in Excel. Simply use the concatenate In Excel function, or the & operator, to combine two or more cells into a single cell.

So, two weeks ago, I showed you how to generate unique usernames in Excel. Even if you have more than one person with the same name, you can create usernames using the COUNTIF function. You can see that blog post right here. I took the first five letters of the user’s first name and second name to generate the username. After receiving a few emails from some Excellers on how to carry out that first step in the process. I completed the first step using the concatenate or the & sign and the LEFT function. So, here is how you do it with concatenate in Excel.

My original name list looked like this.

concatenate in Excel

I needed to take the first letters of each name as that is the naming convention in my database.  This is where we use the LEFT function.  LEFT function allows you to extract a substring from a string, starting from the left-most character. 

The LEFT Function Syntax

Here is a quick recap f the LEFT function syntax

=LEFT(text,number of characters)

text=The string that you wish to extract from

number_of_characters=This is optional.  It indicates the number of characters that you wish to extract starting from the left-most character. If this parameter is omitted, only 1 character is returned.

So, in order to take the first 5 letters of both the first name and second or surname the formula looks like this-

concatenate user names1

I have used the & method, to join the two sets of five letters as it is a quick way to write the formua, but I could just as easily joined the two LEFT functions with CONCATENATE to acheive the same result. You can see how to use concatenate in Excel below.

The Concatenate in Excel Function Syntax

Let’s take a step back and look at The syntax of the CONCATENATE function is-

CONCATENATE( text1, text2, …)

text=the string/s you want to join together.  In this case the formula would look like in the screenshot below which gives us the same result.  Some people use & and other use CONCATENATE in Excel. It is up to your personal preference which you use.

concatenate user names3

So that is how you take the first five letters of the first name and surname, then combine them to generate a username.

In conclusion in this blog post, we showed you how to use concatenate and the ampersand symbol (&) in Excel. We also covered other ways that these symbols can be used to create a username for an account or other purposes. If you want more information on any of the topics discussed here, feel free to click through our site’s articles for help!

Excel Macro

Barbara

Barbara is an experienced data analyst with over 20 years of experience. Author of the How To Excel At Excel Top 50 Excel tips Ebook, YouTuber and Excel Blogger.

Recent Posts