Need formula to replaces multiple spaces in cell with line return

[sspatriots]

Hi,

I have a situation where the user decided to keep hitting the space bar when entering addresses in a single cell to create the next line of the cell, rather than using "alt + enter". How can I replace these spaces with a line return when pulling this information into another cell of another worksheet with a formula.

The cell contents look like this:

John Doe 999-999-9999 Somewhere America, Inc. 5555 Some Highway Suite 99 Anyplace, MD 12345



Any help would be much appreciated. Thanks, SS

 

[sspatriots]

John Doe-------------------999-999-9999-------------Somewhere America, Inc.--------------5555 Some Highway----------------Suite 99---------------------Anyplace, MD 12345

Except those dashes you see are actual spaces...

 

[kweaver]

Have you tried: =TRIM(cell)

 

[sspatriots]

Have you tried: =TRIM(cell)

I tried this:

=IF(CO4="","",TRIM(SUBSTITUTE(XLOOKUP(CO4,'MRL SHPG - In Process'!A:A,'MRL SHPG - In Process'!E:E),CHAR(32)," ")))



However, all that does is remove the multiple spaces and replaces them with a single space. I want to inject a carriage return wherever there are multiple spaces in a the string of text that are back to back.

 

[Fluff]

How about

Fluff.xlsm
	A	B
1
2	John Doe 999 999 9999 Somewhere America, Inc. 5555 Some Highway Suite 99 Anyplace, MD 12345	John Doe 999 999 9999 Somewhere America, Inc. 5555 Some Highway Suite 99 Anyplace, MD 12345
Sheet7

Cell Formulas
Range	Formula
B2	B2	=TEXTJOIN(CHAR(10),,TRIM(TEXTSPLIT(A2," ")))

 

[sspatriots]

Wow, that is slick. I've never heard of a "TEXTJOIN" function in a formula before. Thanks, that was exactly what I was after.

 

[Fluff]

Glad we could help & thanks for the feedback.