How can I remove permanent spaces in a text that are not removed by a trim function? (Unicode 00A0)

Question

t

This string contains two permanent spaces in the end that cannot be removed by a trim function. It seems to be Unicode 00A0 (permanent space). How can these be removed?

Answer 1

I think that Regular Expressions are useful in this case.

In Visual Basic, Tools menu, make sure that there is a reference to "Microsoft VBScript Regular Expressions 5.5".

Check an example:

Dim example As String
example = Chr(&HA0) & "15001000" & Chr(&HA0) & Chr(&HA0)

Dim re As New RegExp
re.Pattern = "^[\s\u00A0]+|[\s\u00A0]+$"
re.Global = True

Dim result As String
result = re.Replace(example, "")

You can add more characters to be trimmed.

Answer 2

Hi @Jens-Uwe Weidt

I suggest you try to use folloing formula to have a check.

First, "00A0" is a hexadecimal number, and its decimal data is 160. (You may use =HEX2DEC("00A0") in Excel to convert the value.)

According to article "TRIM function", this function is not suitable for the nonbreaking space character that has a decimal value of 160.

Then we can use formula =TRIM(SUBSTITUTE(A1,UNICHAR(160),"")) to remove the permanent spaces.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---