In excel, you can join texts together in a cell using the CONCATENATE formula. This formula is the reverse of RIGHT formula, MID formula, LEFT formula and the TEXT-TO-COLUMN function to extract part of the data from a cell. One area where this formula is used is in joining the customer name together. In some customer relationship management (CRM) systems, the first name, middle name and the last name are captured in separately in different fields. As such, when the data are
imported into Excel, they are residing in different columns. Therefore, you need to join the texts together to get the full name of the customers.
The concatenate formula is also used in accounting to join the different levels of the charts of account together, or join the product family with product name to form a more meaningful name for analyzing the sales numbers.
Here is how the concatenate formula works:
Assuming that you have the name “Jason” in Cell A1 and “Khoo” in Cell B1, enter the following formula into Cell C1, excluding the square bracket [=concatenate(A1,” “,B1)]. With the formula, Cell C1 will show the result “Jason Khoo”. The space is created by the “ “ between A1 and B1 in the concatenate formula, meaning to say that you could also add in your own text or symbol into the concatenate formula.
Personally, I do not use the concatenate formula the way it is presented above. Instead, I use the symbol ampersand denoted by “&” to join texts together in a cell. Using the same example, I would join the texts together with the ampersand symbol as follows (without the square bracket): [=A1&” “&B1]. Is it easier? At least it is easier for me.
Share this page:
Would you prefer to share this page with others by linking to it?
Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
advanced-excel.com is in no way associated with Microsoft