Forum Discussion
Separating columns in Excel based on their values
I have a long excel list where column E is used for the "attribute Name" and Column F for the "Attribute Value". I need to break each attribute name into different columns and populate their respective values in the columns.
I have seen tables set up like that before but I have no idea on how to break those down into individual columns.
Any suggestions?
5 Replies
LuisSiqueira Alternatively, look into "Text-to-columns". Use space as the separator. It will split the text into separate columns where-ever it finds a space. Make sure you have enough empty columns for the result to be placed into.
Riny_van_Eekelen You Can use Text to Column function but before that insert about 10 to 15 columns and if you dont do so then it will overwrite the attribute value columns
DevendraJain ???? Why respond to me with a suggestion that I had already made myself?
LuisSiqueira
Try something like this:
=LEFT(E2, SEARCH(" ",E2,1)-1)
=MID(E2, SEARCH(" ",E2) + 1, SEARCH(" ",E2,SEARCH(" ",E2)+1) - SEARCH(" ",E2) - 1)=RIGHT(E2,LEN(E2) - SEARCH(" ", E2, SEARCH(" ", E2) + 1))
Put them in whatever 3 columns you have free (EG. Columns J,H,K) in that order
