MrExcel.com - Your One Stop for Excel Tips & Solutions

Join Two Text Columns

December 09, 2021 - by

Join Two Text Columns

Problem: I have data with first names in column A and last names in column B. I want to merge these two columns into one column.

First name in A2 and last name in B2.
Figure 211. You want to join A2 and B2 into a single cell.

Strategy: You can use the ampersand (&) as a concatenation operator in a formula in column C. You change the formulas in column C to values before deleting columns A and B. These are the steps:

  • 1. In cell C2, enter the formula =A2&B2.

A formula of =A2&B2 smashes the values together, creating NORAHJONES without a space between the words.
Figure 212. Use & to join text.

  • 2. To insert a space between the first name and the last name, join cell A2, a space in quotes, and cell B2, using the formula =A2&“ ”&B2.

  • 3. Copy the formula down to all the cells in the range.

Change the formula to =A2&" "&B2 and you get NORAH JONES. It is still in all caps, but at least there is a space.
Figure 213. Join A2, a space, and B2.

Additional Details: To convert NORAH JONES to Norah Jones, you use the PROPER function. =PROPER(A2&“ ”&B2) will convert the names to proper case. This will work for all your names except names with interior capitals, such as Paul McCartney or Dave VanHorn. After using the PROPER function, you will have to manually fix any names that have interior capital letters. (Some people suggest entering the last name as Mc Cartney, with a space to prevent this problem.) Note: If you like PROPER, consider UPPER and LOWER to convert text to upper or lower case.

Change the formula to =PROPER(A2&" "&B2). Now, Norah Jones in C2 looks fine, as does Bill Jelen in C4. But, Paul Mccartney in C3 does not have the second C in Mccartney capitalized.
Figure 214. There are also UPPER and LOWER functions.

Gotcha: If you delete columns A and B while column C still contains formulas, all the formulas will change to #REF! errors. This tells you that you have a formula that points to cells(s) that are no longer there. You can immediately press Ctrl+Z (or Alt+Backspace) to undo the deletion.

If someone deletes the first name and last name column by deleting columns A & B, then the formula changes to =PROPER(#REF!&" "&#REF!).
Figure 215. Delete A:B, the live formulas in C will change to #REF! errors.

This article is an excerpt from Power Excel With MrExcel

Title photo by Юлія Дубина on Unsplash