• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Eloise T

Active Member
(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.
 
Last edited:

p45cal

Well-Known Member
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))
 

p45cal

Well-Known Member
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.

63387
 

Eloise T

Active Member
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. :)
 
Top