clean none printable unicode chars

[lezawang]

Hi
I have cells as below. I know clean() function will not clean these chars and I will need to do substitute() these chars with "" for example. But I have 2 questions:
1) does that mean I have to create a new substitute() function for each cell? knowing these cells are having different chars? that wont be practical. Is there any better way?
2) How do I know the code of a character? for example, I read and found that [] code is 127 but lets say I do not know that how can I know the code? I went to Insert-->Symbol and tried to find [] but that is very time consuming, is there anyway to search (reverse search)? Thank you very much

=CHAR(127)&" "&"Hello"&" "&CHAR(127)
=CHAR(126)&" Hello "&CHAR(126)
=CHAR(125)&"Hello"
=CHAR(134)&"Hello"
=CHAR(127)&CHAR(127)&"Hello"

 

[Oaktree]

Answering #2 first, CODE is the inverse of CHAR (e.g. =CODE(CHAR(127)) = 127)

For #1 , this would be easier with a UDF in VBA (search the board for "return only alphanumeric" or similar)... but, if you didn't want to go the VBA route, you could try something like:

A2 =TEXTJOIN("",TRUE,IF(MOD(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),{0,48,58,65,91,97,123}),2)=0,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),""))

which is an array formula and must be confirmed with CTRL+SHIFT+ENTER (doing so correctly will result in VBA putting { }s around your formula in the formula bar).
@Jonmo1 has a nice write-up here about the mechanics of a similar formula, specifically why the {0,48,...,123} values are there: https://www.mrexcel.com/forum/excel...ly-contains-z-1-9-characters.html#post1811442