Can't Sum Column Even After Formatting as Numbers

[viet658]

Hi Everyone,

I'm unable to use the sum equation for column B for the different days shown on the spreadsheet. I've tried formatting column B as numbers but it still doesn't work. Any ideas would appreciated.

[twiggywales]

looks like you have additional spaces in your information or somthing at the end of your data

try this

=SUM(--(LEFT(B15:B62,LEN(B15:B62)-1)))

entered with Control + Shift + Enter

[ChemistB]

You have a "160" space after each number.
If you're using a regular keypad with a number pad to the right
select all your numbers in B
CNTRL H to bring up Find and Replace
Replace what: ALT + 0160 (on number pad)
Replace with: leave it blank
Replace All
Did that work for you?

[Pete_UK]

You have a non-breaking space character (code = 160) at the end of each of your numbers, so they are treated as text values, and you can't do arithmetic on text.

A quick way to get rid of them is to highlight column B, then CTRL-H (Find & Replace) and in the Find What box you should hold down the Alt key while typing 0160 on the numeric keypad, leave the Replace box empty, and then click on Replace All.

Hope this helps.

Pete

[martindwilson]

there are spaces after the numbers in col b but its char(160) try find alt+0160 (from the numric keypad only)replace with nothing

[viet658]

Thank you guys for the quick response. You guys are awesome.

[Moo the Dog]

Weird, this must be because I am on a Mac or something, but the character at the end returns code 202 for me, and not 160.
I used: =CODE(RIGHT(B15,1)) and it returns 202

Char(160) on this Mac is a cross symbol

- Moo

[martindwilson]

The character encoding used by your computer depends on the operating system used - Windows uses the ANSI character set, whereas Macintosh uses the Macintosh character set.

Therefore, the characters returned by the Excel Char Function for specific number codes may differ across operating environments