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.
Conversation
As you comment, let's keep the discussion friendly and respectful to add value to the conversation.
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse varius enim in eros elementum tristique. Duis cursus, mi quis viverra ornare, eros dolor interdum nulla, ut commodo diam libero vitae erat. Aenean faucibus nibh et justo cursus id rutrum lorem imperdiet. Nunc ut sem vitae risus tristique posuere. uis cursus, mi quis viverra ornare, eros dolor interdum nulla, ut commodo diam libero vitae erat. Aenean faucibus nibh et justo cursus id rutrum lorem imperdiet. Nunc ut sem vitae risus tristique posuere.