Remove leading and trailing spaces but not spaces in the middle of a string

[NewOrderFac33]

Good afternoon,

That's it, really. I'm comparing two lists that look identical at first glance, but some have trailing and leading space characters.
I can't use TRIM as this would remove all the space characters from within the string too, which I don't want.

As an enhancement to this, could anyone suggest a way to replace multiple space characters within a string with a single space, regardless of how many occurrences of multiple sequential spaces were found?

e.g. Cat{Space}{Space}{Space}Dog{Space}{Space}Horse would become Cat{Space}Dog{Space}<space></space><dog><space></space>Horse

As always, thanks in advance.

Pete</dog>

 

[AhoyNC]

Actual I think the TRIM function should do what you want. It will leave 1 space between words.

Excel Workbook
	A	B	C
1	cat dog horse	cat dog horse
2
Sheet

 

[Gary McMaster]

I can't use TRIM<dog></dog>

Trim will remove the leading and trailing space(s) and leave only one space between words. Isn't that what you've described?

Gary

 

[NewOrderFac33]

I stand corrected, colleagues - I thought that TRIM removed ALL spaces. Thank you both.

 

[NewOrderFac33]

To finish this thread off, whilst =TRIM() in Excel removes spaces as described above, TRIM() in VBA only removes leading and trailing spaces - NOT those within the string.

To get the result I want in VBA, I have to use:

Selection.Formula = Application.WorksheetFunction.Trim(Selection.Formula)

Thanks again for your assistance & have a good evening

Pete