Bad format in cell?

Question

What is wrong with cell K26? Whatever function I apply, it gives me "VALUE!" error. If I type in "-25" by hand it works, like I have done in cell K5. The number type is the same. There seems to be only a tiny stylish difference barely visible in the formula bar, yet they are both Arial fonts. Hint: in K6 there is an invisible space character before the minus sign. I tried to get rid of it with the search and replace but it doesn't find the space character. Any idea how to fix this issue?

PS: Perhaps it helps to add that this was a .csv file that has been transformed to an xlsx. Maybe there is a setting in the conversion that should be taken into account?

Answer 1

Opss... now I realize that I was copying and pasting an html sheet from a webpage into Excel. One must paste it with the "paste special" option and then select "text" format. It is a bit contrived and complicated to edit all the cells, but I guess there is no magic wand that makes a quick conversion from html... right?

Anyway, for the time being, I go along with this solution.

Answer 2

That CSV import step explains everything.

What’s happening isn’t just random invisible characters but it’s a side effect of how Excel interprets text encoding during CSV import.

You can re-import correctly:

Create a new blank workbook.

1. Go to Data → Get Data → From Text/CSV.

Select your .csv again, and set File origin = UTF-8.

Load it → Save as .xlsx.

That will produce a clean file where all numeric fields are truly numeric.

Answer 3

Thanks for the screenshot, Marco! That narrows down the issue. There’s an invisible character (a non-breaking space or similar Unicode character) before the minus sign in K6. That can be deleted manually, here's how:

• Click the cell and enter edit mode (F2).
• Place the cursor before the minus sign.
• Press Backspace once.
• Hit Enter.

Test again.

Best regards,

Kimberly