Why the =SUBSTITUTE(A1,CHAR(160),"")+0" formula to remove space from the end of a value is not working?

HansV wrote:

Try this formula:

=--SUBSTITUTE(B2,CHAR(160),"")

or

=VALUE(SUBSTITUTE(B2,CHAR(160),""))

The result should be no different from =SUBSTITUTE(B2,CHAR(160),"")+0 , which is what we see in K2.

And in fact, if I simply cause K2 to be recalculated, it works just fine on my computer.

Therefore, I suspect the problem is: Shazia's computer (or Excel instance) does not interpret the string "48,278" as a number after removing the last character (!).

So, it seems that comma is not treated as either the decimal point or the thousands separator (!).

Quick-and-dirty work-around: Deselect the Advanced Option "Use system separators", and set the "Decimal separator" option to period (dot), and set the "Thousands separator" to comma, or vice versa.

EDIT.... If comma should be interpreted as the thousands separator, as simpler alternative might be change the formula in K2 as follows (adding some generalization):

=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(B2, LEN(B2)-1), ",", ""), CHAR(32), ""), CHAR(160), "")

That removes the comma, any and all normal and nonbreaking spaces, and the last character, then converts to a number.

And alternatively, if you know the comma should be a different character, replace the first red-highlighted null string ("") with the replacement character in double-quotes (e.g. ".")

PS.... If that does not work around the problem, there might some other inexplicable problem with Excel on your computer. Ostensibly, that might mean that you need to repair or reinstall your Office product.

But before you try that draconian (and risky) procedure, try the following experiment. Close all instances of Excel. Then in a new instance of Excel, manually type 48,278 into A1 (with the comma). What does =ISNUMBER(A1) return? If TRUE, what does =INT(A1) return?

Also, manually type =VALUE("48,278") into A2. What does it return?

And again, before you try to repair or reinstall Office, review your computer configuration. And if you are using cloud services or online Office 35, try to review their relevant configuration. (I don't know much about that.)

You're correct (of course)!

Thanks! Try this formula:

=--SUBSTITUTE(B2,CHAR(160),"")

or

=VALUE(SUBSTITUTE(B2,CHAR(160),""))

Hi there, the copy of the workbook can be accessed through the following link:

MACROS_Monthly Flash Report.xlsm

Many thanks

Could you create a stripped-down copy of the workbook demonstrating the problem (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.