How to add "remove ASCII character 160" to macro line

[Eloise T]

(See macro line below) CLEAN takes care of ASCII 0-31 and TRIM takes care of extra spaces but I also need to remove ASCII char. 160 as well.
Is it possible to add "remove ASCII character 160" to the following line and if so, how? ...or I'm also open to an alternate suggestion to accomplish the same thing.

.Value = .Parent.Evaluate(Replace("IF(#>"""",TRIM(CLEAN(#)),"""")", "#", .Address))

Thanks in advance.

 

[AlanSidman]

Look at post #4 at MrE. -->https://www.mrexcel.com/forum/excel...n-printable-special-characters-well-trim.html

 

[Eloise T]

Look at post #4 at MrE. -->https://www.mrexcel.com/forum/excel...n-printable-special-characters-well-trim.html

Those appear to be overly complicated.

 

[Marc L]

As it depends on what .Value and .Address refer to …​

​

 

[p45cal]

Guessing: it's going to be something along the lines of:
.Value = Evaluate(Replace("IF(#>"""",SUBSTITUTE(TRIM(CLEAN(#)),CHAR(160),""""),"""")", "#", .Address))
which will remove the non-breaking spacce and replace it with nothing, however should you want to replace the non-breaking space with a normal space then:
.Value = Evaluate(Replace("IF(#>"""",SUBSTITUTE(TRIM(CLEAN(#)),CHAR(160),"" ""),"""")", "#", .Address))

 

[Eloise T]

Is the second """"), needed? ..or maybe the better question, what does it do?

 

[p45cal]

Is the second """"), needed? ..or maybe the better question, what does it do?

Turned into an actual formula it becomes something like:
=IF($A$1>"",SUBSTITUTE(TRIM(CLEAN($A$1)),CHAR(160),""),"")

The first empty string is asking if the cell contains something.
The second empty string is what replaces the non-breaking space (ascii 160) in the SUBSTITUTE function and the one I suggested you consider using a normal space otherwise 2 words might be concatenated with no space. If you miss it out altogether the formula doesn't complain, so it may indeed not be necessary.
The third is what shows if A1 is empty. If you miss this one out, and A1 is empty then you get FALSE.

 

[Eloise T]

THANK YOU! Now I understand.

BTW, I did determine that ".Parent." had to remain as the macro formula wiped out all the data in several tabs. Fortunately I keep backups.