4 ways to combine text values in Excel

Combining text values is a common task in Excel. There are many ways to do it, but let's focus on a few function-based methods.

In the example, there's a list of first names (A3:A8) and last names (B3:B8) housed in separate columns. The goal is to combine them into a single column of full names.

To start with, a CONCAT formula is used for each row to join together the two values with a space in between.

E.g. =𝙲𝙾𝙽𝙲𝙰𝚃(𝙰𝟹," ",𝙱𝟹)

TEXTJOIN is then used in a similar way. This function has the advantage of only needing a delimiter specified once. That's not an issue here, however.

E.g. =𝚃𝙴𝚇𝚃𝙹𝙾𝙸𝙽(" ",,𝙰𝟹:𝙱𝟹)

Whilst these approaches work, they require six formulas, which isn't good practice in modern Excel.

One solution is CONCATENATE, a deprecated function still present for compatibility reasons. Referencing the ranges is found to spill the names exactly as desired, contrary to CONCAT and TEXTJOIN, which make them appear in the same cell.

=𝙲𝙾𝙽𝙲𝙰𝚃𝙴𝙽𝙰𝚃𝙴(𝙰𝟹:𝙰𝟾," ",𝙱𝟹:𝙱𝟾)

=𝙲𝙾𝙽𝙲𝙰𝚃(𝙰𝟹:𝙰𝟾," ",𝙱𝟹:𝙱𝟾)

=𝚃𝙴𝚇𝚃𝙹𝙾𝙸𝙽(" ",,𝙰𝟹:𝙰𝟾,𝙱𝟹:𝙱𝟾)

Although CONCATENATE does the job, it's old. The alternative method and the best out of the lot is to use the ampersand operator to 'glue' together the strings in the ranges, leaving a space in between.

=𝙰𝟹:𝙰𝟾&" "&𝙱𝟹:𝙱𝟾

For more Excel tips and tricks like this, check out our Video Tutorials page.