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 agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.