Formulas and Functions

[wmcfall]

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?

[#2]

@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.

[#3]

@HansVogelaar 

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

[#4]

@wmcfall 

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

[#5]

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

[#6]

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.

[#7]

Happy to share Patrick.....thanks

You can view "Book1.xlsx" at: https://acrobat.adobe.com/link/track?uri=urn:aaid:scds:US:0d08d8bb-8adc-4f77-b737-9c726eb40e34@Patrick2788 

[#8]

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.