Re: "It leaves the trailing spaces"

Most likely those spaces are Character 160 (called a non-breaking space).

This formula removes almost all unnecessary characters...

=SUBSTITUTE(CLEAN(TRIM(A2)), CHAR(160), "")

This formula gets just the non-breaking spaces...

=SUBSTITUTE(A2, CHAR(160), "")

The 'Clean Data' utility in my free "Professional_Compare" workbook will do

it for you without having to use a helper column.

Download from OneDrive...

https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

'---

Nothing Left to Lose

After spending a looooong time searching on the net, I found out here that there is also another character for space, the output of Chr(160) and for some reason Trim functions ignore it. So the solution in my case was firstly replace all these 'different' spaces and then trim values:

Worksheets(2).Range("D17").Value = Replace(Worksheets(2).Range("D17").Value, Chr(160), " ")
Worksheets(2).Range("D17").Value = Trim(Worksheets(2).Range("D17").Value)

In case you encounter a similar problem and run into a different-from-Chr(160) 'unknown' character, you can find out what this character is, by isolating this character and using the Asc function.

Create a UDF that uses VBA's version of Trim which does not touch the inner spaces. Only removing the leading and trailing spaces

Function MyTrim(str As String) As String
    MyTrim = Trim(str)
End Function

Then you can call it from the worksheet:

=MyTrim(A1)

If you want a formula to do it:

=MID(LEFT(A1,AGGREGATE(14,6,ROW($XFD$1:INDEX(XFD:XFD,LEN(A1)))/(MID(A1,ROW($XFD$1:INDEX(XFD:XFD,LEN(A1))),1)<>" "),1)),AGGREGATE(15,6,ROW($XFD$1:INDEX(XFD:XFD,LEN(A1)))/(MID(A1,ROW($XFD$1:INDEX(XFD:XFD,LEN(A1))),1)<>" "),1),999)