Sign in or start a free trial to avail of this feature.
3. Combine Strings of Text
We often need to combine multiple cells of text into one cell and to do this we use '&'. Find out how to use this very useful command correctly in this lesson
&: Chain text strings together
LOWER: Change cell text to lowercase
ALT + I , C: Insert column
F2: Jump back into cell formula
Combining text strings together in Excel is very easy and one of the most common commands you'll perform when manipulating data.
In this lesson, we're going to take the first and last name for each user and create a username, which will be the lowercase first name, followed by an underscore, followed by a lowercase last name.
We’ll start by creating a new column with Alt + I C, and we'll widen this using the mouse.
I'll entitle this column Username and I'll start by writing equals and including the first name.
To chain the first name to the last name, we use the ampersand sign, which on most keyboards is Shift + 7.
And then to include the last name in this string, I'll simply select it, which is H2, and press Enter.
And as you can see, the ampersand sign chains the first name and the last name entries together in one cell.
However, we still have a lot of work to do.
Our first task will be to change the last name to lowercase.
So let's re-enter the formula with F2, and we replace H2 with the function “lower” and then the cell H2 passed into this function.
If I now press Enter, you can see that we now have the first name and the last name both in lowercase.
All we have to do now is include the underscore.
So let's return to our formula again and press F2 to jump back inside.
When you're chaining strings and you'd like to include a character such as an underscore, you simply wrap that character n double quotes and then connect it to the other elements using the ampersand sign.
So I'll move in position to the first ampersand sign, open double quotes, write an underscore, close double quotes, and then write another ampersand sign to connect the underscore to the last name.
If I now press enter, you can see we now have the user name perfectly created for Kaci Rawlins.
Using the ampersand sign, we can combine strings of text in almost any way we want.
To fill out the username for the remaining entries, we can just select the cell and double click the bottom right hand corner with our mouse.
To get some more practice at chaining strings together, try to create a new column called Full Name that separates the first name and the last name with a space and has both values in the capitalized format.
I'll leave my answer in the after file which can be downloaded underneath this video.