Take the 2-minute tour ×
Ask Different is a question and answer site for power users of Apple hardware and software. It's 100% free, no registration required.

When a row in a table from a webpage is copied to the Numbers app, such as

2       3    4      5

and another row is copied, as some "deltas" (minor addition or subtraction):

+0.25   0    0   -0.5

Then the numbers are added up vertically. The number in the first column adds up to 2, while the last column adds up to 4.5. So the first column is incorrect, and the last column is correct -- I think the reason is +0.25 is treated as a string, while -0.5 is treated as a number.

Is there a way to make it work on Numbers? (manual editing is not preferred, as it is error prone and there are actually a lot more numbers, and finding the + by eye and removing it can cause an error when it is not seen, and if there are 200 such numbers, then doing it 200 times by hand is quite a time consuming method.)

(and actually, this happens on Google Apps as well)

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

You can use the Search/Find functionality (Cmd-F) in Numbers to replace the + with "nothing". Don't worry about + in formulas, they won't get replaced. Summing the the rows should work ok afterwards.

share|improve this answer
    
I guess this should work as long as we don't have other comments or line that has the side effect when the + is removed... work well when there are only numbers in the form of +0.25 –  動靜能量 Jul 26 '12 at 23:04
add comment

You can remove the + by using =VALUE(SUBSTITUTE(A1,"+","")).

share|improve this answer
    
so you mean, if I have a table that is 20 x 6 cells, then I have to copy and place another 20 x 6 cells on the same sheet (or probably on another sheet), with that formula to "translate" the numbers... so in a way, there will be double the real estate (area or cells) used? –  動靜能量 Jul 26 '12 at 21:37
    
You could do it all at once, perhaps something like =A1+VALUE(SUBSTITUTE(B1,...))? –  jtbandes Jul 26 '12 at 21:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.