How to replace space with newline

Hi all,

I have space separated characters in A1 as:

Red Blue .. ..

I want to replace space in newline in new column cell, such as :
Red <---- in A1
Blue <---- in A2

I try replace space with "ctrl+j", but replaced characters remain in a single cell..

Hi
this macro should take the contents of cell A1 and type all the separate "words" (separated by spaces) in successive rows starting in B1:

Please Login or Register  to view this content.

Thanks.. It works fine for one cell, but I have many such cells (from A2 to BWM2), and want to convert them simultaneously..

Could the name name and destination be set as such?

Set SourceCell = Range("A2") 'address of cell to split
Set DestCell = Range("A3") 'address of first cell to copy to

it can be tweaked - do you want all the the split values to continue in the same column; if so, try this:

Please Login or Register  to view this content.

@Nicky, I'm not sure you need both loops, maybe you can do something like this:

Please Login or Register  to view this content.

Which might be a little quicker.

Thanks yudlugar
Yes – that’s a more economical way to do it!