Seperate Comma seperated values in seperate rows in different tab of same worksheet

Dear Pros,

This may be easy for you but yet would mean a lot to me,


Basically in the attached file are two tabs ,one is the tab which will be present and the other tab (worksheet ) is the one i would want the output to resemble ,

In tab1 of attached sheet "Seperate comma values.xlsm" ,you will see that column F has at times single entries but at other times multiple entries seperated by a comma ,

Heres what i want ,please observe tab 2 titled Final ,the values are now split to different rows ,the values are enlarged in text size and given a color and one blank row is left after the last entry ,

Say i have 6 values in Sheet 1 in say a certain cell F6 for column F ,in Final tab ,i want This 6 values to go in F6,F7,F8,F9,F10,F11 and a blank row F12 below this ....wish for the entries to have a larger text size (but that i can myself manage ),

Would like some help,this forms a part of a bigger project ,

I have always been helped in this forum ,i would like some assistance in this case ,if possible ,

Thanks to anyone who would take some time to attempt it ,


Humble Regards ,


Amlan Dutta

Hi Amlan
try it

Please Login or Register  to view this content.

Dear Nilem ,

Give me just 30 minutes to revert back with results of Input and Output sheet .But let me take 5 seconds to thank you for your time .You have no idea of how much i appreciate .

Please accept my sincere heartfelt thanks for the quick prompt response you have provided ,

I will shortly convey to you whether it works ,in either case ,i am deeply thankful ,

Regards ,


Amlan Dutta

Dear Nilem ,

It works like a dream ,i attach both the input sheet and output sheet

I put your brilliant piece of art (macro )in a module and assigned it to Blue Button "Comma seperate value in new row " ...if you press the button ,then the output would get generated as in Output sheet (also attached )

This is too good ,works like magic ! seen that you used Boolean method to turn off Screen updating (Pure genius ),i never knew that we can assign values in this manner to make it false ,

I wished that it would come in a new tab(worksheet) though so that the Input file still remains so that i know what i had so as to compare ,

But still tremendous work ,i will mark thread as solved and give you stars ,

Thanks a tonne again ,

If you had a chance ,please do look at my thread at this link http://www.excelforum.com/excel-prog...abs.html,which is a extension of this project ,i will deeply appreciate if i can have your inputs on it

i am deeply thankful for what you have shared ,i am fascinated by the instr property now ,

Humble Regards ,

Amlan Dutta .

Originally Posted by amlan009

...I wished that it would come in a new tab(worksheet) though so that the Input file still remains so that i know what i had so as to compare...

try

Please Login or Register  to view this content.

Dear Nilem ,

This Works like a charm ,you are my sweetest talented russian friend this day onwards)))!!!!!!

Thanks so so so so so so so so much !

I wish that i can give you more stars ,the rules don't permit !

The case stands solved with brilliance ,

I can't thank you enough ,i am so grateful ,

Find both Input and Output sheets (using your macro code )attached ,they work like magic !

Humble regards ,

Amlan Dutta

На здоровье

He he ,what does that mean ?

I guess i can't ask that because that would mean thread hijack or something ,but thanks for solving the case ,

Cya around with some more interesting cases !

Regards ,