Data Analysis involves text manipulation and transformation tasks. The analyst might need to split the content of a cell into separate cells, for instance. In contrast, analysis tasks might require the analyst to join the contents of individual cells together to form one text body.
If text transformation involves cell split, the Text to Columns wizard is the best option to use. Whereas joining cells can be achieved in various commonly used methods. One common method is using the ampersand (&) to join cells. It is usually used for quick, short concatenation. Simply, insert the ampersand between cell references -or text surrounded by double-quote- and you get the result.
=A1&”-“&B1
Another way to join cells is using the CONCAT() function, which is now phasing out the CONCATENATE() function in Excel. With this function, it is easier to combine the text from multiple ranges and/or strings. This is easier than using the ampersand especially for multiple cells or long ranges since you don’t need to select individual cell references as in using the (&). However, CONCAT() doesn’t provide an argument to let the user specify the delimiter to use in concatenating the cells or ranges. Hence, you need to write the delimiter in double-quotes after each cell reference or text to delimit it from the subsequent one. This leads us to the recent TEXTJOIN() function in Office 365 that remedies the shortcomings of (&) and CONCAT().
=CONCAT(A1,”-“,B1)
With the TEXTJOIN() function you can combine the text from multiple ranges and/or strings with the ability to specify a delimiter to use between each text value that will be combined. Moreover, the function includes an argument that you can use to skip empty cells within the range to combine.
= TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
=TEXTJOIN(“-“,TRUE,A1:B1)
This movie illustrates combining multiple cells into one using the three methods explained above.
