Formula to remove excessive spacing

[wigi]

Hello all,

I couldn't find an answer through the search function so here goes.

Is there a formula-based way (non-VBA) to reduce excessive spacing to just 1 space ? For instance:

ABC (6 spaces)DE

would result in:

ABC (1 space)DE

All cells contain plain text and the formula will be in a different area. I want to avoid VBA and if possible Lambda functions. Probably we need an array formula.
When there are multiple instances of excessive spaces in a cell, it's fine to have them all converted.

Many thanks !

Wim

 

[Michael M]

Am I missing something in the example I would use
=TRIM(A1)

 

[wigi]

It's also in the middle of the string

 

[Michael M]

Yep, and TRIM will remove excess spaces in the middle of the string as well.....but are they really spaces
Have you tried using
=CLEAN(A1) as well

 

[wigi]

Oh, I didn't know/forgot about, that one ! I assumed Trim would take away leading and trailing spaces but leave anything in the middle untouched.
I'll experiment a bit because the actual case is more involved than only this part but thanks for pointing me to this.

 

[Michael M]

Glad to help...

 

[wigi]

Works good and it explains why I couldn't find difficult array formulas or Lambda functions for it, it's just the TRIM function

 

[Michael M]

Sometimes we don't see the forest for the trees....

 

[Peter_SSs]

I assumed Trim would take away leading and trailing spaces but leave anything in the middle untouched.

Perhaps you were thinking of the vba Trim() function that works that way.

 

[wigi]

Perhaps you were thinking of the vba Trim() function that works that way.

Exactly, and how it is different from Application.Trim in VBA.