From your question it’s not entirely clear to me what you want to do. Obviously you want to differentiate one product (Product ID) from another. So, do you just want to change a listing that looks like:

Product ID: 32
Product ID: 21
Product ID: 72
etc.

into:
Product ID
32
21
72
?

That’s easily done.

Add a row at the top of your data (Insert / Row) at the place where you want it.

Type the header name for the column: Product ID

Now select the cells that have the prefix “Product ID” that you want to remove and do a Find-and-Replace step:

Find “Product ID: ” (include the colon and the space, if one exists)
Replace ”” (two double-quotes with no space between them; this will replace the “Find” text with <nothing>, leaving you with only the product ID values.

Was that what you wanted?

If you want to copy a vertical columnand paste it into a horizontal row then:
Copy – Paste Special – Transpose

Excel 2011 for Mac: Using the Paste Special Feature

Indeed, @CWOTUS, this is what I am looking to do. The reason I am looking for a shortcut of some kind is that I have many columns that are set up like this, for example:

Product SKU: 1233
Product SKU: 1234
Product SKU: 1235

Or:

Product Name: A
Product Name: B
Product Name: C

So there will be many columns and rows with different sets of repeating data. To do this manually would be tedious, especially twice a month. Your technique is an excellent solution, though I might look to build on that.

I still prefer @CWOTUS ‘s method, but if you insist, you have one more option. You can insert a blank column beside the column in which you have the data. Enter in the header as pointed out by @CWOTUS, and then use the function SUBSTITUTE.

For example, if you have “Product ID” as header in the cell A1, and data “Product ID: 32” in the cell A2, then you can use the following formula in the cell B2.

=SUBSTITUTE(A2,“Product ID: ”,””)

The rest is just to drag the formula down. And, if you still want to automate it further by taking in the header and adding to it the colon and the space, you can do so as in the formula below.

=SUBSTITUTE(A2,CONCATENATE($A$1,”: ”),””)

And, drag the formula down till the data ends. You can double click drag handle when it turns to a plus sign, it will go down by itself. That should further save some time for you.