Excel Tip: How to Merge and De-Merge Information

Written by:  Jeanne Franche

As a bookkeeper, I use Excel on a daily basis.  It is an enormous time-saver, and a very useful tool.  Therefore, I get very excited when I learn new tricks to get more use out of the program - and here I will share two recent tricks that I have learned with you.

The Concatenate Function

Let's say you run a little restaurant, and you have an Excel sheet to track which drinks and foods are the most popular in your store.  You have flavour in Column A, and Type in Column B.

You want to Subtotal this information, but the Subtotal tool in Excel will only total one column or the other (Flavours or Types).  You want to know how many units of "Tomato Juice" you sell, and also how many "Tomato Sandwiches" you sell.  The solution is to "concatenate" the two columns together, and then you can use the Sort and Subtotal tools (Covered in a future blog post).

How?

Decide which column you want the new Concatenated data to be in.  I chose Column C.  Click "Insert a Function", search for "Concatenate".

In "Text 1" choose the first data cell you want to Concatenate.  In "Text 2", you can enter a separator (recommended) such as a dash (-), though it is not necessary.  In "Text 3", chose the second data cell you want to Concatenate.  Example below:

Click "OK" and then your cell C will have the new, concatenated data (Orange-Juice).

Application

I typically use this function paired with sort and subtotal (which I will cover in future blog posts).  You can use this for virtually anything (such as, in the example above - tracking sales) to more specific uses.

A tip to anyone using Microsoft Small Business Financials:  This is a handy tool to use for exported Smart List account reports, to Concatenate accounts and projects that are displayed in separate columns.

How to "Un-Concatenate" 

Let's say you export some information from your accounting program (or CRM software) and the information is all together in one cell.   One example I can think of is client names and addresses.

Row A

John Doe, 123 Somewhere Street, Wherever, Ontario, P04 3Z5

This is frustrating, because you want to sort all of your clients based on city, or province, or by last name - but everything is together in the same cell!  How do you separate it?

First - you must select your column or cell, go to "Data", and then "Text to Columns".

Step 1:  You will have the option between fixed width or delimited.  I chose "delimited" because my data is separated by commas.  Fixed width will give you greater control over where to separate your data (i.e. You can have "Somewhere" and "Street" separated), but for simplicity - I've chosen delimited.

Step 2:  Select "Other" and enter the character that separates your text - in our case it's a comma.  You will see a preview of what your selection will do.

Step 3:  This screen will allow you to choose the format of each column.  In this example, I clicked Finish - but this can be useful if you have numerical values that you want to sum, or dates that you want to sort.

And Voila!  Now you're free to filter and sort based on whatever you wish, or simply reorder the columns (put last name in column A, first name in column B for example).  Now your data is much easier to manipulate!

And that's it!

Thank you for reading my blog - I would love to hear your feedback in the comments section below!