The word concatenates simply means “to combine” or “to join together”. The CONCATENATE function in excel permits you to combine text from different cells into one cell.
As a financial analyst, we frequently deal with varied data when doing financial analysis. The data is not always organize for analysis and we often need to combine data from one or more cells into one cell or split data from one cell into different cells. The CONCATENATE function helps us to do the combining and splitting of data. The CONCATENATE in excel helps us to structure data in the specified format.
Excel Formula for CONCATENATE
=CONCATENATE (text1, [text2], …)
How To Use CONCATENATE In Excel
Below is the example, where we have a list of contact information for each person with respective rows, and columns for each person’s first name, last name, and other contact information. Now, we want to combine the data in the columns, Last Name and First Name to appear together in the one cell, but doing it manually would take a long time.
We can solve this problem quickly simply by using the CONCATENATE function rather than combining this data manually.
Before using the function, we’ll have to insert a new column in our spreadsheet for this new data. In our example, column C is inserted right of column B.
Now write the function into cell C2. To begin, we will put the equals sign (=) followed by the function name =CONCATENATE(…. We need to tell the CONCATENATE function what cells to combine. In our example, we want to combine the text in cells A2 and B2, to form the full name as an end result.
Writing the function =CONCATENATE(B2, A2)
Please note CONCATENATE in excel will combine exactly what you tell it to combine, and nothing more or less. Like in this example, the first and last names don’t have a space in between them after combining.
While analyzing and presenting data, we may require to combine data in such a way which may include commas, spaces, various punctuation marks, or other characters such as a hyphen or slash. In case you want to present your data with punctuation, spaces, or any other special details to appear in the cell, you’ll have to inform CONCATENATE to include it.
For doing this we need to insert the character we want in our concatenation formula, but we need to enclose that character in inverted commas. A few examples are shown below:
CONCATENATE in Excel with space, comma, etc
Formulas Used:
Now we can use the drop down fill handle to copy the formula to the remaining cells in this column.
Please note that the CONCATENATE function in excel concatenates or joins up to 30 values together and returns the result as a text.
Also read our blog on How to Transpose in Excel: https://thepowerexcel.com/blog/how-to-transpose-excel-data/