Combine Values Into One Cell in Microsoft Excel Power Query

If you want to combine values ​​in Microsoft Excel instead of complex expressions or VBA, go with Power Query—it’s fast and easy.

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

This tutorial walks you through combining values ​​in a single cell using the Microsoft Excel Power Query Column from Examples function. Specifically, we combine address elements into a single cell for fast 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 in Excel Power Query

Let’s say you have an Excel spreadsheet of names and addresses similar to the one in Figure A. You want to combine the address elements into a single cell. You end up with two columns of data – the names and the addresses – that are easy to fit into a quick shipping label run.

Figure A

Reading the data into Power Query requires just a few steps.

1. Click anywhere in the Excel spreadsheet.

2. Click the Data tab.

3. In the Get & Transform Data group, click From Grid/Range. The data must be a table object, but don’t worry – if it’s not a table, Power Query will prompt you to format it as a table before proceeding.

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, in last-first-name format. For mailing labels, if you want the name in first-last-name format, you can achieve 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 Samples. From the drop-down list, choose From selection, which adds a new column to the grid.

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

Figure C

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

Figure D

In this simple example, you don’t win much; However, if you have tens or even thousands of records, this feature comes in handy. This is a simple illustration of the flexibility of this function – 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 does more than just 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 City, State, and ZIP Code address columns. To create a multi-column selection, click the first column heading. Then hold down the Ctrl key while clicking the others.

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

3. Type in the first cell of the new column 111 Little Street, Smallville, KY 55555. Instead of pressing Enter or Tab, press Ctrl + Enter. This time, Power Query recognizes the pattern immediately (Figure E).

Figure E

4. In the above pane, click Ctrl + Enter or press OK to accept Power Query’s suggestions.

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

Figure F

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

As a general guide, I recommend that you select the columns you are working with and press Ctrl + Enter after typing each example value.

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