Convert Excel cell data to a column header?
Asked by
metadog (
381
)
September 24th, 2012
Hi! I have an Excel spreadsheet exported from an online e-commerce site I am working on. I get a column of cells that show something like this:
Product ID: 32
Product ID: 21
Product ID 72
And so on…
What I would like to do is to have Excel take “Product ID” and set it as the column header and delete it from the cells. Is there a way to do this? I have many similar cells (Product SKU: 1234, Product Quantity: 3, Produce Name:... and so on). I will be getting this report twice a month and I would hate to have to do all of this manually. I am an Excel newbie, so any help would be greatly appreciated! I have heard about Macros, but haven’t gone down that path yet. I am using Excel for Mac 2011 (no flames about Mac, please… this is what I was given).
Thanks!
-M
Observing members:
0
Composing members:
0
4 Answers
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?
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.
Answer this question 
This question is in the General Section. Responses must be helpful and on-topic.
Composing members:
0
