When it gets to the point that you find yourself working in excel on a daily basis, it’ time to start learning some shortcuts to help you become more efficient and better at your day-to-day tasks. One of the most common formulas I use in excel is the transpose and concatenate formula. This is a super easy way to combine a range of cell data without having to do any VBAs or advanced formulas.
Combined, these formulas allow you to easily combine a range of cells without having to individually type them in with just the concatenate function alone. There are various reasons you may want to combine spreadsheet data, but as a digital marketer, I find myself using this formula to combine keywords into one cell before sending it over to a client.
Transpose, Concatenate Formula – No Commas or Spaces
- You’ll need a range of cells with your data listed in them. In this example, we’ll say your data is in A2:A75.
- Select the cell where you want the data combine, let’s say B2.
- In B2, type in =TRANSPOSE(A2:A75). You can quickly select the range of cells by clicking in A2, and then clicking CTRL+SHIFT+↓. Do not click enter.
- Before clicking enter, click the F9 button.
- At the beginning of the formula, delete TRANSPOSE{ and replace it with CONCATENATE(. Go to the end of the formula, and replace the } with ). Your formula should look like this now: =CONCATENATE(“A2″,”A3″,”A4″,”A5″,”A6″,”A7”, …).
- Click enter.
All of your data should now be combined into one cell now. At this time, you can CTRL+C to copy and right-click paste ‘Values Only’ in a new cell so that only the content is in the cell and the formula is gone. If you want spaces and commas, use the next formula instead.
Transpose, Concatenate Formula – With Commas and Spaces
- You’ll need a range of cells with your data listed in them. In this example, we’ll say your data is in A2:A75.
- Select the cell where you want the data combine, let’s say B2.
- In B2, type in =TRANSPOSE(A2:A75)&”, ” (remember the space after the comma). You can quickly select the range of cells by clicking in A2, and then clicking CTRL+SHIFT+↓. Do not click enter.
- Before clicking enter, click the F9 button.
- At the beginning of the formula, delete TRANSPOSE{ and replace it with CONCATENATE(. Go to the end of the formula, and replace the } with ). Your formula should look like this now: =CONCATENATE(“A2″,”A3″,”A4″,”A5″,”A6″,”A7”, …).
- Click enter.
All of your data should now be combined into one cell now, and have the appropriate commas and spaces. At this time, you can CTRL+C to copy and right-click paste ‘Values Only’ in a new cell so that only the content is in the cell and the formula is gone.
Just a heads up, This works when the listing is in a column (A1:A75) when you use a listing that is from left to right (A1:S1) it generates a semi colon where the comma is supposed to be (; instead of ,) using the same steps, you could put a ” in the beginning of the formula to be able to get out of it, then select the cell and do a search for the the semi-colon ; and replace all with a comma. After that just remove the ” at the beginning of the formula and change transpose to concatenate and replace brackets with parenthesis. Ultimately getting the same result, but from a list that is written from left to write.