Excel offers multiple text functions to generate lists for users. We’ll go through an example of using Excel’s text functions.
The text functions in this Excel example used are join, left, right, and length.
In the example spreadsheet, you will make a fictional computer username and password derived from a person’s first and last names.
To generate a username from the data, start by formatting Excel to combine the person’s first and last name.
Enter the following in cell C2:
Use the fill handle to drag down from there over to cell C6 to fill in the rest of the table.
Next, you are going to compose the length of the generated username. Do this by inputting the following in cell D2:
Again, use Excel’s fill handle to drag from there to cell D6 to fill in the rest of the table.
How to create a company email address in Excel
From the usernames you generated, you can easily create a company email address. Do this by taking the first letter of the first name and the entire last name. Next, enter the following in cell E2:
=LEFT(A2,1) & B2 & “@company.com”
Use the fill handle to drag to E6 and fill out the rest of the table. This action will create emails for each username entry.
Finally, learn how to make a formula for the ‘Generated Password’ field.
To generate a password, you will learn how to display and use the “right” function to grab letters from the right side area of cell text. For the password in the example, you will use the last three letters of the first name, the last three letters of the last name, and finally the username length. Start by entering the following:
Input this formula into cell F3:
=RIGHT(A3,3) & RIGHT(B3,3) & D3
Use the fill handle and drag down to cell E6 to fill in the rest of the table. This generates a unique password for each user. Tweak the number of letters used to change passwords as needed.
- Excel Right Trim
- How to Prepend Text to Cells
- Change Case of Text in Excel
- Get Initials from Names in Excel
- Excel Left Function
View this offer while you wait!