Excel Formulas, help!

%3CLINGO-SUB%20id%3D%22lingo-sub-2282304%22%20slang%3D%22en-US%22%3EExcel%20Formulas%2C%20help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2282304%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20wish%20to%20break%20down%20a%20describtion%20of%20an%20item%20into%20different%20sections%20of%20coulumbs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20for%20example%2C%20lets%20say%20it's%20written%20%22Computer%20554x374x16%20(244mm%20c%2Fc)%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20want%20it%20to%20be%20divided%20into%20%22Computer%22%2C%20%22554%22%2C%20%22374%22%2C%20%2216%22%2C%20%22244mm%20c%2Fc%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20would%20make%20it%20so%20much%20easier%20for%20me%2C%20would%20appreciate%20any%20help%20given.%20I'm%20open-minded%20and%20I%20can%20adjust%20the%20text%20slightly%20if%20needed.%20(Perhaps%20it's%20better%20with%20L554%20B374%20T16%2C%20rather%20then%20%22x%22'%20between.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2312144%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%2C%20help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2312144%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1034677%22%20target%3D%22_blank%22%3E%40MaxS95%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt's%20better%20to%20use%20same%20delimiter%2C%20e.g.%20space%2C%20everywhere.%20With%20that%20it%20could%20be%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20894px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F277138i23FA2D416888C369%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eformula%20is%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20TRANSPOSE(%20FILTERXML(%22%3CROOT%3E%3CNODE%3E%22%26amp%3BSUBSTITUTE(A1%2C%22%20%22%2C%22%3C%2FNODE%3E%3CNODE%3E%22)%26amp%3B%22%3C%2FNODE%3E%3C%2FROOT%3E%22%2C%22%2F%2Fnode%22)%20)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello!

 

I wish to break down a describtion of an item into different sections of coulumbs.

 

As for example, lets say it's written "Computer 554x374x16 (244mm c/c)"

 

I would want it to be divided into "Computer", "554", "374", "16", "244mm c/c".

 

This would make it so much easier for me, would appreciate any help given. I'm open-minded and I can adjust the text slightly if needed. (Perhaps it's better with L554 B374 T16, rather then "x"' between.

3 Replies

@MaxS95 

It's better to use same delimiter, e.g. space, everywhere. With that it could be

image.png

formula is

= TRANSPOSE( FILTERXML("<root><node>"&SUBSTITUTE(A1," ","</node><node>")&"</node></root>","//node") )

Hello Sergei!

 

You're on the right track, it's something like this that I'm seeking for, much appreciated!

I can't divide it with only spaces like you suggested though, it needs to be written in a certain way. Would you mind trying to figure it out?

 

Computer 554x374x16mm (244mm c/c)    

(Computer) (554) (374) (16) ((244mm c/c))

@MaxS95 

If you could use without double brackets like

(Computer) (554) (374) (16) (244mm c/c)

it could be

image.png

Or to use couple of more nested SUBSTITUTE() for them.