Excel: Join Together Strings Using Concatenate

Concatenate! It's a bizarre-looking word if you've never seen it before, but the application's actually pretty simple AND very useful.

So here's a scenario for you: You have a spreadsheet listing contact information for hundreds of people. Last names are in column A, first names are in column B. But you need the full name now, for some reason. Here's where concatenate comes in!

Concatenate lets you basically take different pieces of text (called "strings") and put them together. Yes, you can do this with numbers as well, just know that the end result will be formatted as text (there's a way to make it a number, but that'll be a topic for another post). In this case, you're wanting to take the first name, put a space in between, and then have the last name show at the end. Here's the formula to use:

=CONCATENATE(item1,item2,item3,item4,etc.)

Just write "=CONCATENATE", then put in brackets each item you'd like to join together, separated by commas. You can put in cell references, formulas, or even other pieces of text. If you want specific text to show up, make sure you put it in quotes.

So for my example, assuming there's a last name in cell A2 (DuBois), and a first name in cell B2 (Stacy), I'd enter this formula into cell C2:

=CONCATENATE(B2," ",A2)

That's saying: take whatever is in cell B2, then put in a space, then put in whatever's in cell A2. So for this example, my formula would return:

Stacy DuBois

And of course, you're free to fill this formula down through your datasheet and watch as all of the names (or whatever it is you're doing) are automatically generated.

Some people like to use this to generate usernames. You can use the LEFT function to pull the first letter of the first names, and use concatenate to join it with the last name.

The possibilites are endless!
Read More