Excel Text Function Tutorial

Excel’s text functions can be used for charts, presentations, and general data organization. Follow the Excel Text Function Tutorial to learn how to utilize this feature.

Download the example spreadsheet to follow along.

In the spreadsheet example, the text functions used are “join”, “left”, “right”, and “length”.

Here you will create a fictional computer username and password from the data of a person’s first and last name, and will generate the character length of the total compiled string.

To create the username, start by formatting Excel to combine the person’s first name with their last name.

In cell C2, input this formula:

=a2&b2

Drag the fill handle function from there to cell C6 to fill out the rest of the spreadsheet.

Next, compile the length of the created username by entering the following in cell D2:

=len(c2)

Use the fill handle down from there to cell D6 to fill out the spreadsheet, just as you did previously.

In order to create a company email address from the created username, take the first letter of the person’s first name and the entirety of their last name. Next, input the following formula in cell E2

=LEFT(A2,1) & B2 & “@company.com”

Drag the fill handle down to cell E6 to fill in the spreadsheet. This will make email addresses for each entry.

How to create a formula for the ‘Generated Password’ field

Next you can create a password and learn how to use the “right” functions in Excel to grab letters from part of the cell text. For the password creation example, use the last three letters of the person’s first name, the last three letters of their last name, and the number for the username length. Start with the following formula in F3:

=RIGHT(A3,3) & RIGHT(B3,3) & D3

Use the fill handle down to cell E6. This automatically generates a unique password for each user.