I need some help in sorting my data. As in the image, the original file looks like the top image and I want to split the size into a new row below for all items as in the bottom image.

before and after

The file contains multiple columns that look like this. All content are separated with comma. I want to split these data into a new row under the original item for 1000 items per page. Either function or VBa will be really helpful .

I am using Excel 2016 .

  • 1
    What did you already try? At which point during that do you get stuck? – Hennes Jul 1 '16 at 8:02
  • i can only do it with convert text to columns wizard , then copy and paste with Transpose,but it seem gonna take forever to edit the whole file, and i have many file to edit. I am willing to learn ,do you have any ideal that could make it easier ?thank you – Jackson Teo Jul 1 '16 at 8:28
  • You've asked for a worksheet-function. I'm not sure, but I don't think it will be possible. It is possible in VBa but you don't state if VBa is an option – Dave Jul 1 '16 at 8:31
  • Hi Dave , sorry i didnt mention that ,and thank you – Jackson Teo Jul 1 '16 at 8:34
  • Can I recommend you look at the following post and its answers for inspiration: superuser.com/questions/1094215/… As you'll see, there are options for both formulas and VBA. Even though your use case is slightly more complex, the solution can be very similar. – jehad Jul 1 '16 at 9:04

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.