Replace ";" with CTRL+OPT+ENTER to enter notes on a new line in the same cell

[lawi227]

I don't believe this can be done with the "Replace All" feature of Excel.

Example:
I want to take the text in this cell below:

the dog ran; the cat slept; I have two dogs - they're both poodles

<tbody>
</tbody>

And run a VBA code (unless there is an easier way) to space the sentences on separate lines within a cell

the dog ran the cat slept I have two dogs - they're both poodles

<tbody>
</tbody>

The cells are I2:I133. Note: There are some cells that are blank. I am using Excel for Mac.

 

[RickXL]

Hi,

I think you can still use the Replace functionality.

However, you need to have completed the Excel ninja, supergeek, 7th dan, hacker course first.

Enter the semi-colon in the "Find what" box then insert Ctrl + J in the "Replace with" box. It won't look as if anything has been inserted into the "Find what" box but it will replace the semi-colon with an ASCII char 10 when you hit the Replace button. I think the fact that J is the tenth letter may be more than coincidence but that is pure guess work on my part.


Regards,