How to combine values into a single cell in Microsoft Excel Power Query

When you want to combine values in Microsoft Excel, instead of complex expressions or VBA, opt for Power Query – it’s fast and easy.

My TechRepublic article How to combine values from a column into a single cell using Microsoft Excel’s Power Query uses Microsoft Excel Power Query to group data and then combine all values for that group into a single cell. The grouping requirement is what complicates this example – combining values into a single cell is much easier if you don’t need to accommodate a group.

This tutorial walks you through how to combine values into a single cell using Microsoft Excel Power Query’s Column From Examples feature; specifically, we’ll combine address elements into a single cell for a quick label run. I’m using Microsoft 365 on a Windows 10 64-bit system, but Power Query is available through Excel 2010. You can download the demo file for this Excel tutorial.

SEE: How to start an Excel accounting system (TechRepublic Academy)

How to get data into Excel Power Query

Let’s suppose that you have an Excel table with names and addresses, similar to the one in Figure A. You want to combine the address elements into a single cell. You’ll end up with two columns of data – the names and the addresses – which will be easy to drop into a quick mailing label run.

Figure A

Getting the data into Power Query requires following just a few steps.

1. Click anywhere inside the Excel Table.

2. Click the Data tab.

3. In the Get & Transform Data group, click From Table/Range. The data must be a Table object, but don’t worry about that – if it isn’t a Table, Power Query will prompt you to format it as a Table before continuing.

Figure B shows the data in Power Query.

Figure B

How to use Columns From Examples to transpose in Power Query

The first thing you might notice about the data is that both names are in one field, and they’re in last, first name format. If you want the name in first last name format for mailing labels, you can accomplish this by using Columns From Examples.

1. Select the Name column.

2. Click the Add Column menu.

3. In the General group, click Columns From Examples. Choose From Selection from the dropdown, which will add a new column to the grid.

4. Enter Susan Harkins. You want to enter the first name in the format you want, as that’s how Power Query learns the pattern. As you can see in Figure C, Power Query suggests the format for the remaining cells correctly.

Figure C

5. Press Ctrl + E to accept the suggestions (Figure D).

Figure D

In this basic example, you don’t gain much; however, if you have dozens or even thousands of records, this feature comes in handy. This is a simple illustration of this feature’s flexibility – now let’s use it to combine the address values.

How to use Columns From Examples to combine values in Power Query

Power Query’s Columns From Examples feature can do more than transpose values – it can also combine multiple values into a single cell. To illustrate, let’s combine the address elements into a single cell.

1. Select the address columns, City, State and ZIP Code. To create a multi-column selection, click the header of the first column. Then, hold down the Ctrl key while you click the others.

2. Click Columns From Examples and then choose From Selection, as you did in the previous example.

3. In the first cell of the new column, enter 111 Small Street, Smallville, KY 55555. Instead of pressing Enter or Tab, press Ctrl + Enter. This time, Power Query picks up the pattern immediately (Figure E).

Figure E

4. Click Ctrl + Enter or press OK in the above pane to accept Power Query’s suggestions.

This time Power Query needs only one example to fill in the remaining cells (Figure F). Pressing Ctrl + Enter after each example entry can help, but it won’t always make any difference.

Figure F

To load the data into Excel, click Close & Load in the Close group on the Home tab.

As a general guideline, I recommend that you select the columns you’re working with and press Ctrl + Enter after entering each example value.

There are other ways to combine values into a single cell, but Power Query’s Columns From Examples feature is a breeze. If you want to stay in Excel, read How to concatenate values in a single Excel column to a single row. The problem is a bit different, but you can use the concatenation operator & and the TEXTJOIN() function.