Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
I have a file to split can someone give me an idea what to use
I have a column which has listed differnt attributes and i want to split the attributes into their own columns
so for instance 1 cell has the following info
colour=red;size=38;material=polyester;manufactured=italy
so ideally i want to split the values into seprate columns but also if possible move them into specific columns
for instance i want colour to be in column M
Im sure there is a formula or something to do it? Thanks
Was going to suggest something like this - but someone's already made a page on it:
Does this help?
I would select all your data and first use find & replace, to change all the equals to semi-colons, then use text to colmnns using the semi-colons as delimiters which will split all your values into single cells. Depending on the quality of the text (I.e. is each initial value laid out in the same way with no gaps etc.) will influence what you can do from there. Good luck!
RM.
Off the top of my head I can't remember what it's specifically called but it's split yo columns or something. Its a big button in the data ribbon.
Moving it into specific columns.... Once? Or will this be repeated? Is the data the same everytime?
Loads of ways, manual, new transposing sheet, macro etc. Nothing hard just what effort is warranted.
over complicating matters.
Under the Data ribbon, Data tools section fifth from the left on mine, "Text to Columns"
Set the = and the ; both as your delimiters, and then scrub out the columns you dont need.
What Olly said, anything else is just making life hard for yourself
Surely text to columns will only give useful output if the same attributes are in the same order on every row?
Surely text to columns will only give useful output if the same attributes are in the same order on every row?
And if it's a one-off.. a wee formula will work without any faffing clicking/selecting etc.
@euain has posted a good link.
I've got something working with formulas with this spreadsheet...
A1 = Thing
B1 = Attributes
C1 =
D1 = colour
A2 = Jumper
B2 = colour=red;size=38;material=polyester;manufactured=italy
C2 = =MID($B2&";",SEARCH(";"&D$1&"=",";"&$B2)+LEN(D$1)+1,999)
D2 = =LEFT(C2,SEARCH(";",C2)-1)
This shows 'red' in D2.
Hide column C
Change the column heading in D to retrieve a different attribute
Duplicate columns C and D (e.g. to E and F) to retrieve more attributes
You'll need to convert "s to quotes if you copy and paste the formulas above.