Andrew Moss
Technical Writer
May 23, 2023
May 23, 2023

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.

@globalexcelsummit 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. =π™°πŸΉ:π™°πŸΎ&" "&π™±πŸΉ:π™±πŸΎ #exceleration #excel #microsoftexcel #excelformulas #exceltips #globalexcelsummit ♬ original sound - globalexcelsummit

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.

Join the Master Club

Your exclusive all-access pass to our entire digital learning experience for a whole year.