New to Me Excel Function for Combining Cells
Situation: you have multiple columns of excel data and you need to combine them together. You’d like to use a delimiter between each data element with a hyphen or maybe a space.
For our example lets use a product SKU. You have STYLE, COLOR, SIZE and you’d like to combine them to make your SKU. The table looks like this:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | style | color | size | sku |
| 2 | BOB | BLUE | M |
Previously I would have populated cell D2 with on of these two formulas to the result I’m after, BOB-BLUE-M:
=(A2&"-"&B2&"-"&C2)=CONCAT(A2,"-",B2,"-",C2)
NEW TO ME TODAY, you can use:
=TEXTJOIN("-", TRUE, A2:C2)
The first part of the formula is your delimiter. The TRUE is if you’d like to ignore empty cells or not, in this case we are. And the last section of formula is the cells you’d like to combine. Now you’re saying “but hey that’s more characters than the other two formulas”, and you’d be correct. But what if you have 10 columns of data? Let’s compare:
=(A2&"-"&B2&"-"&C2&"-"&D2&"-"&E2&"-"&F2&"-"G2&"-"&H2&"-"&J2)=CONCAT(A2,"-",B2,"-",C2,"-",D2,"-",E2,"-",F2,"-",G2,"-",H2,"-",J2)=TEXTJOIN("-", TRUE, A2:J2)
🎵 The more you know 🌈
