I showed you how to break text in one column into multiple columns using the Text to Columns tool in a previous Tip, but today I’m going to show you how to string text together. In other words, same problem, just going in a different direction.

You can’t add words together…

As you can see, I’ve got a list of first names in Column A and last names in Column B. You might be inclined to write a formula that would add the two together, like this:

=A2 + B2

Unfortunately, you cannot add words together. If you try, you’ll get the #VALUE! error message, just like I did in the video.

String ‘em together

What you can do, is string the words together using the ampersand (&) instead of a plus sign. The revised and corrected formula would look like this:

=A2 & B2

Unfortunately, doing that is going to shove the two names together with no space between them.

Give a little space…

You can always insert text into a string by enclosing it in quotation marks. So we can revise and correct that formula one more time, like this:

=A2 & ” ” & B2

The space inside the quotation marks is called a literal, and it can be anything you want. If, for example, you wanted to format your new column as LastName, FirstName you could write your formula like this:

=B2 & “, ” & A2

After I get my formula correct, all I have to do is use the Fill Down command and all 1,000 names have been put back together.

Feel free to download the sample file and let me know what you think about this technique in the comments section.