VBA Code to Split a cell value into 2 or more cells

[SaurabhShri1]

Hi,

I have a value in a cell(say A1 cell) => "This is a versatile macro" and i want this cell value to be splitted in 5 rows like
This
is
a
versatile
macro

How could i do this?

Please help guyz

Refer the attached excel

[Grizar]

I think you could do something like this:

Please Login or Register  to view this content.

and the Main function

Please Login or Register  to view this content.

You may need to debug this, because I do not have an Excel on my current laptop. Especially the Instr sentence, because I sometimes make mistake using it (I often put parameters 2 & 3 in the bad order).

Hope it will helps.

[tigeravatar]

This can be done with formulas, but if you really need a macro, it can be done as a one-liner:

Please Login or Register  to view this content.

[tigeravatar]

To make the macro more versatile, this version will prompt first for the string to split, and second for the user to select the output destination starting cell:

Please Login or Register  to view this content.