Join texts together
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.
Have your say about what you just read! Leave me a comment in the box below.
Share this page:
Enjoy this page? Please pay it forward. Here’s how…
Would you prefer to share this page with others by linking to it?
- Click on the HTML link code below.
- Copy and paste it, adding a note of your own, into your blog, a Web page, forums, a blog comment, your Facebook account, or anywhere that someone would find this page valuable.
<a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a><a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a>
Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
advanced-excel.com is in no way associated with Microsoft