4

I have some cells with text like

1200 1200 1400

And I would like to get the sum of the numbers in another cell. I think it is impossible to do using an excel formula since there is no loop, but I may be wrong. For example, I can get a string with a formula using =CONCATENER("=";SUBSTITUE(P25; " ";"+")) (sorry, I use Excel in French), but I cannot evaluate it.

Labo
  • 203
  • 2
  • 7
  • 1
    You can remove new lines in the formula by using =SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),""),CHAR(10),"") – Leo Chapiro Jan 06 '16 at 10:47
  • 2
    Have a look at [Excel function that evaluates a string as if it were a formula?] : http://superuser.com/questions/253353/excel-function-that-evaluates-a-string-as-if-it-were-a-formula – Dirk Horsten Jan 06 '16 at 11:02

1 Answers1

1

No, you can't use one formula for this. Either use VBA or do something like

="="&SUBSTITUTE(SUBSTITUTE(A1,CHAR(13),"+"),CHAR(10),"+")

Then copy - paste special - paste values, then edit the cell and press enter. That's the quickest I can figure out as INDIRECT() won't work.

Raystafarian
  • 21,583
  • 11
  • 60
  • 89