Forum Discussion
Impossible to sum numbers in Excel
philip369 I had the same problem when cutting and pasting from a web page. Numbers were text. All I did was a Replace of space to @ then replace the @ with nothing. Trying to replace space with nothing did exactly that - nothing - it didn't remove the spaces.
Text to columns did nothing, I have yet to come across anything where that function works to be honest and I've been using Excel since it came out (I'm an old programmer!).
copy/pasting from web usually adds non breaking space (char code 160), which is not printable character. You may check with =LEN(A1) and compare with number of visible characters.
On 365 or 2021 you may check exact codes with =CODE( MID( A1, SEQUENCE(LEN(A1), 1) ).
Or rightmost code as =CODE( RIGHT(A1) ). In general it could be other non-printable characters. If they are you may remove them
=1*SUBSTITUTE(A1, CHAR(160), "" )
with 1* to convert result to number.
