Forum Discussion
Another issue with Value transformation.
I've played for a couple of days with this, having tried a number of different solutions that worked for others. Cannot figure out how to make a text field convert to value. Here is my XLS. I am runn...
For Excel 365, this will strip out just about anything (even stuff CLEAN can't handle).
=--REGEXREPLACE(B6,"[^A-Za-z0-9 .]|[\x{00A0}\x{200B}-\x{200D}]|[\t\n\r]|[\x{1F600}-\x{1F64F}]","")
This was part of a diagnostic lambda I had created called Revealλ which provided a cell-for-cell description of what Excel saw in each cell (e.g. Number, number stored as text, special character, etc.).
Yes, \s covers not all white spaces. On the other hand, in this case we may use something like
=--REGEXREPLACE(B6, "[^\d|,|.]", "")