Formulas and Functions

Copper Contributor

I copied values from an imported spreadsheet onto another to work the data.  When I tried to auto sum the values the total is 0.0 but the formula shows up in the header bar.  It does this even after I format the data as numbers, thinking it was originally entered as text.  What can I do to get the formulae working?

 

7 Replies

@wmcfall Select the values in a column.

On the Data tab of the ribbon, click Text to Columns.

Select Delimited, then click Finish.

If necessary, repeat for other columns.

@Hans Vogelaar 

When I go to the data tab there is no Text to Columns, instead I see this:

 

wmcfall_0-1675226859009.png

 

@wmcfall 

Text to Columns is just visible on the right hand side of your screenshot.

S2209.png

Thank you for pointing that out. I followed your instructions, but I still end up with an auto sum of zero???
If Text to Columns isn't able to re-evaluate Column C as a number, you may have something else in that column that's causing Excel to see it as text. It may help if you're able to share an anonymized sample workbook with a few of those values from Column C.
It looks like your numbers have ANSI character 160 in front of them. At the sheet level this character appears as a space. To resolve, go into any cell with a number and select the character that precedes the number. Go into find and replace. In the find box, paste in the copied character. Leave Replace blank and select replace all.