sum of values returns zero despite changing cell format to numbers

Hi learn_mac,

You are right, it is a formatting issue. You may notice that after you change the format to numbers or currency the cell reverts to automatic. With the leading "S" Numbers will be interpreting this data as text. One quick way to observe this is that text is left justified in Numbers.

=VALUE(MID(B1,2,10))

Will remove the initial "S" and provide a number value.

quinn

When you want Numbers to treat a cell entry as a number it should not contain a currency symbol or a "+" Those should be added through formatting.

Another way (just adding to Ians suggestion) is to use the substitute function:

C1=SUBSTITUTE(SUBSTITUTE(B2,"$",""),"+","")

this is shorthand for... select cell C1, then type (or copy and paste from here) then formula:

=SUBSTITUTE(SUBSTITUTE(B2,"$",""),"+","")

This a nesting of substitute functions like this:

<PREV1> = SUBSTITUTE(B2,"$","")

C1=SUBSTITUTE( <PREV1> ,"+","")

<PREV1> is a made up temporary variable name to hold an intermediate result after substituting "$" for nothing (""). That is, then, used as the input to the next substitute that removes the "+"

you can fill down as needed by selecting cell C1, copy

select cells C1 to the end of column C, paste

A script is efficient at removing extraneous characters. No formulas, extra columns etc. Just one click.

1. Copy-paste into Script Editor (in Applications > Utilities).
2. Change numCharsToRemove if you have more or less than 2.
3. Select the cells that you need to clean up.
4. With the cells selected, click the triangle 'Run' button.

SG

property numCharsToRemove : 2

tell application "Numbers"

tell document 1 to tell active sheet

set t to first table whose selection range's class is range

repeat with c in t's selection range's cells

set v to c's value

set c's value to v's text (numCharsToRemove + 1) through -1

end repeat

end tell

end tell