Formula to Add Hard Space

[legalhustler]

I have the following various length text string:

Hello World 123 abc; Love Excel 365

I would like to substitute the ";" plus one space with a hard space so that I get my results to show in two separate lines like this:

Hello World 123 abc
Love Excel 365

Can someone assist with the formula?

Thanks!

 

[legalhustler]

It is an old teletype control character for a line feed (it moved the typing down one line) that Microsoft's QBASIC, VB and VBA has spotily maintained. Some control characters are sometimes still (but not always) supported such as in the Find or Replace dialogs. Note that if you look carefully after issuing the CTRL+J keystroke the Find or Replace dialog box, you can see a small blinking dot along the bottom of the text field... that is the blinking text cursor located on the line below showing through... you can see this by pressing the left arrow after issuing the CTRL+J keystroke and the text cursor will appear in the text field... then if you next press the right arrow, the blinking dot will reappear.

Yes, I saw that. Are there any other useful letters? Note I don't have a number pad on my laptop.

 

[Rick Rothstein]

Yes, I saw that. Are there any other useful letters? Note I don't have a number pad on my laptop.

I am not aware of any. I would note that CTRL+J does not work everywhere throughout VBA, so there is no apparent "rule" when or where any control characters can be used.