Combine Data From Multiple Cells

BY STANELY ZAROWIN

Q: I have an inventory-tracking problem. We record the inventory in our retail stores with Excel. Each item is identified with three sets of numbers. For example, a three-digit code (248) indicates the store’s identity, another three-digit code (564) signifies the department, and a four-digit code (1223) is for the product— and each is recorded in a separate column, like this:

 

 

Our tracking would be much easier if, at times, we could combine the codes into one number. Can you help?

 

A: What you want to do is concatenate—that funny word which means to connect or link. In computer lingo it means to gather data from several cells and combine them into one cell, which is what you want to do.

 

The basic use of Excel’s Concatenate function is:

 

=CONCATENATE(text1, text2, text3,…)

The screenshot of the spreadsheet below illustrates the Concatenate function with the example inventory codes supplied above, with column F showing the linked code.

 

 

Notice also how I created spaces between the assembled codes by inserting pairs of quote marks (“), like this:

 

=CONCATENATE(text1,” “,text2,” “,text3,”…)

 

You can streamline this process by using the ampersand (&), which in Excel programming means Concatenate. Here is the above formula using the ampersand rather than the Concatenate function:

 

 

Or, if you want to add a dash (-) between the groups of data, add dashes within the quote marks, as shown below:

 

 

SPONSORED REPORT

Revenue recognition: A complex effort

Implementing the new standard requires careful judgment. Learn how to make significant accounting judgments and document them and collaborate with peers for consistent application.

VIDEO

How to Excel pivot a general ledger

The general ledger is a vast historical data archive of your company's financial activities, including revenue, expenses, adjustments, and account balances. J. Carlton Collins, CPA, shows how to prepare data for, and mine data with, PivotTables.

QUIZ

News quiz: Taking an economic snapshot and looking to the future

Recent news included IRS actions that affect individuals and partnerships and a possibly influential move by a Big Four accounting firm.Take this short quiz to see how much you know about the news.