Formulas and Functions

Question

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?

patrick2788 · Answer

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.

hansvogelaar · Answer

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.

wmcfall · Answer

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

hansvogelaar · Answer

wmcfall&nbsp;
Text to Columns is just visible on the right hand side of your screenshot.

wmcfall · Answer

Thank you for pointing that out. I followed your instructions, but I still end up with an auto sum of zero???

Forum Discussion

Formulas and Functions

Share

Resources