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!

 

[mikerickson]

It sounds like you want a linefeed rather than a hard space.

=SUBSTITUTE(A1, "; ", CHAR(10))

 

[Rick Rothstein]

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?

Give this formula a try...

=SUBSTITUTE(A1,"; ",CHAR(10))

Note that you will have to turn "Wrap Text" on for the cell that you put this formula in (and maybe widen the cell's column) so that the returned value will display on multiple lines.

 

[Fluff]

Use the replace box
Select the col you want > Ctrl H > enter "; " (without quotes) in the Find What box > Ctrl J in the Replace with Box > Replace All

 

[legalhustler]

It sounds like you want a linefeed rather than a hard space.

=SUBSTITUTE(A1, "; ", CHAR(10))

Ah! I was using CHAR 160 when I needed a line feed....thank you!

 

[Rick Rothstein]

Ah! I was using CHAR 160 when I needed a line feed....thank you!

CHAR 160 is a non-breaking space used on webpages so that a line of text will never occur at its location.

 

[legalhustler]

Woah, what is ctrl J? That is pretty slick.

 

[legalhustler]

Use the replace box
Select the col you want > Ctrl H > enter "; " (without quotes) in the Find What box > Ctrl J in the Replace with Box > Replace All

Are there any other shortcut letters that do different things? This is the time I've seen this.

 

[Rick Rothstein]

Woah, what is ctrl J? That is pretty slick.

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.

 

[Fluff]

Ctrl J mimics Alt Enter in a cell & inserts a new line.
Some shortcuts can be found here https://www.asap-utilities.com/excel-tips-shortcuts.php