Remove leading and trailing spaces keeping multiple spaces between words intact

[MikeJ100]

How can I remove leading and trailing spaces keeping multiple spaces between words intact? I notice the TRIM function removes multiple spaces between words .

 

[arthurbr]

You could eventually use Power Query ? Remove excess spaces in Power Query - Excel Off The Grid ( your profile does not specify your Excel version)

 

[SanjayGMusafir]

Check this -

All Records.xlsb
	C	D
3	Sanjay Gulati Musafir	Sanjay Gulati Musafir
Sheet2

Cell Formulas
Range	Formula
D3	D3	=SUBSTITUTE(SUBSTITUTE(C3," ","",LEN(C3)-LEN(SUBSTITUTE(C3," ","")))," ","",1)

 

[Peter_SSs]

Check this -

I don't know what is possible with the OP's data but your formula seems to only allow for a single leading space (not more, not less) and a single trailing space (not more, not less). If that were the case, this would do the same job
=MID(C3,2,LEN(C3)-2)

@MikeJ100
As suggested above, please update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have all the latest functions then you could try this formula solution for any number of leading/trailing spaces, including none.

22 10 10.xlsm
	A	B	C	D	E
1				For info, trailing spaces in col A	To show no trailing spaces in col B
2	Big Dog	Big Dog		0	Dog
3	Big Dog	Big Dog		2	Dog
4	Big Black Dog	Big Black Dog		4	Dog
5	Big Dog	Big Dog		1	Dog
Trim Left & Right Only

Cell Formulas
Range	Formula
B2:B5	B2	=LET(s, SEQUENCE(LEN(A2)),c,MID(A2,s,1),ts,TEXTSPLIT(TEXTJOIN("|",1,IF(c=" ","",s)),"|")+0,MID(A2,MIN(ts),MAX(ts)-MIN(ts)+1))
E2:E5	E2	=RIGHT(B2,3)

 

[MikeJ100]

Check this -

All Records.xlsb
	C	D
3	Sanjay Gulati Musafir	Sanjay Gulati Musafir
Sheet2

Cell Formulas
Range	Formula
D3	D3	=SUBSTITUTE(SUBSTITUTE(C3," ","",LEN(C3)-LEN(SUBSTITUTE(C3," ","")))," ","",1)

Hi Sanjay, thanks for your post.

When I use the formula you suggested I get

 

[Peter_SSs]

Possibly an easier way to go, especially if you do not have the functions used in my post above, is to employ a very simple user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function LRTRIM(s As String) As String
  LRTRIM = RTrim(LTrim(s))
End Function

MikeJ100.xlsm
	A	B
1
2	Big Dog	Big Dog
3	Big Dog	Big Dog
4	Big Black Dog	Big Black Dog
5	Big Dog	Big Dog
Trim Left & Right Only (2)

Cell Formulas
Range	Formula
B2:B5	B2	=LRTRIM(A2)

 

[MikeJ100]

I have updated my account details. I am using 365 and Windows. Sadly, Peter's suggestion doesn't work.

 

[Peter_SSs]

Sadly, Peter's suggestion doesn't work.

That gives us nothing to go on.

Does it crash Excel?
Does it produce an error value? If so what?
Does it do nothing?
Does it remove all spaces?
Something else?

Thanks for updating your version though.

 

[MikeJ100]

Thanks for your help, however when I use Peter's suggestion for a macro and his formula I get

 

[SanjayGMusafir]

I have updated my account details. I am using 365 and Windows. Sadly, Peter's suggestion doesn't work.

If you are using the formulae I gave or @Peter_SSs gave there are negligible chances of getting error. But still if you are telling, it means there is some problem somewhere.

If you may post the actual formulae you have posted to identify where the challenge could be...