Excel expert wanted

[robinessex]

I'm being driven bonkers by the what I believe is the Excel CHAR 160 issue. For the hell of it, I'm writing an Excel spreadsheet to solve sudoku puzzles. Using the COUNT functions, I've hit the problem of this character interfering with formulas in cells. Has anyone come across this, with a have a solution ? Tried the net of course, but still, nothing that seems to really help.

[sideways sid]

Would this help? copied from the link here:

https://www.ablebits.com/office-addins-blog/remove...


....then most likely there are some non-breaking spaces or non-printing characters in your worksheet.

To get rid of them, convert non-breaking spaces into regular spaces with the help of SUBSTITUTE:

SUBSTITUTE(A2, CHAR(160), " ")

Where 160 is the code number of a non-breaking space character ( ).

Additionally, use the CLEAN function to eliminate non-printable characters:

CLEAN(SUBSTITUTE(A2, CHAR(160), " "))

Nest the above construction in the TRIM function, and you'll get a perfect formula to remove spaces before/after text as well as non-breaking spaces and non-printing characters:

=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))

Edited by sideways sid on Friday 15th October 13:14

[robinessex]

sideways sid said:

Would this help? copied from the link here:

https://www.ablebits.com/office-addins-blog/remove...


....then most likely there are some non-breaking spaces or non-printing characters in your worksheet.

To get rid of them, convert non-breaking spaces into regular spaces with the help of SUBSTITUTE:

SUBSTITUTE(A2, CHAR(160), " ")

Where 160 is the code number of a non-breaking space character ( ).

Additionally, use the CLEAN function to eliminate non-printable characters:

CLEAN(SUBSTITUTE(A2, CHAR(160), " "))

Nest the above construction in the TRIM function, and you'll get a perfect formula to remove spaces before/after text as well as non-breaking spaces and non-printing characters:

=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))

Edited by sideways sid on Friday 15th October 13:14

It doesn't work for me, as I have an array that's 81x81x80 sheets, and getting bigger every day. I've been to the Ablebits website, I have their Excel Addins, which include a cleaner function, but that excludes formulas, which isn't of any use to me.