Forum Discussion

philip369's avatar
philip369
Copper Contributor
Dec 05, 2019

Impossible to sum numbers in Excel

I hope someone can help with this because I am pulling my hair out.  When I try to sum numbers in a column, at the bottom of the page it gives me a count and not a sum.  When I try to Autosum, it g...
excel
terryloo's avatar
terryloo
Copper Contributor
Sep 18, 2024
That's because a hidden character somehow snuck in the numbers and prevents you from using those as numbers. CHAR(0160) didn't work for me.

Only 100% foolproof way that I found was to
1) save the excel file as a *.txt
2) open the txt file, select the whole file (CTRL + A), the hidden text characters will now appear as ?
3) Find replace ? with nothing.
4) Copy paste the whole thing back to excel.
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Sep 18, 2024

    This is a discussion that seems to go on forever!

     

    Another option, that has come about recently, is to forget about characters you do not want and focus on extracting those you require.  For a single cell you might have

    = VALUE(CONCAT(REGEXEXTRACT(@import, "(\d|\.|-)+", 1)))

    but for an entire array of imported data,  this becomes,

    = MAP(
     import,
     LAMBDA(data,
        VALUE(CONCAT(REGEXEXTRACT(data, "(\d|\.|-)+", 1)))
     )
  )

    Life gets more complicated if you need to pick out dates, numbers in scientific notation etc.

Resources