How do I add all the numbers in a give cell without manually inputting the = sign in the cell? For example: I have a 1000 rows with entries such as: R1: 25+123+34 R2: 34+45+321 R3: 65+123+345 etc I want to know what the sum of those numbers equal, and the only way I know is to insert = sign in front of each cell and that would take forever. Anyone know a shortcut
Why the aversion to the “=” sign? All formula’s must have the = sign
in the column to the right of this data use the formula =“xyz”&A1 copy and paste the values of the new column do a find and replace xyz with = it’s ugly but it works, anyone know a prettier way?
=CONCATENATE("=",A1)
MFE Wrote: ------------------------------------------------------- > =CONCATENATE("=",A1) then copy and paste values, then replace = with #, then replace # with =… done
If you put into Excel +2+4 you will get 6.
Why would you use CONCATENATE() when a simple “&” will do?
macro
Goto Insert, Name, Define Name: EvalFormula Refers to: =Evaluate(B1) Hit “ok” Column A 25+123+34 34+45+321 65+123+345 etc… Column B ="="&A1 ="="&A2 ="="&A3 etc… Column C =EvalFormula =EvalFormula =EvalFormula etc… Column C should then show 182 400 533
data —> text to columns —> delimited by ‘+’
none of them worked PS: I am using Office 2000. I can only get as far as turning the cells into =234+567+890 but how do I get excel to actually calculate the formula? It just shows it as is but won’t calculate.
hmmm… i was able to get everyone’s to work except wanderingcfa’s. are you doing a copy-paste values before the find and replace?
nvm I forgot to copy and paste. Now it works. I am also having trouble figuring out wanderingcfa and gamblingeconomist ways.
gambling econmists way in a little more detail: 1) highlight the data in column A you want to be able to sum. 2) on the menu go to data->text to columns 3) click next and then on step 2 uncheck ‘tab’ and check ‘other’ and put a ‘+’ in the box next to it and click finish. 4) now all of the numbers are separated into columns so all you have to do is sum them with a sum() function
Sorry guys, I forgot one step. Select Cell C1…Then… Goto Insert, Name, Define Name: EvalFormula Refers to: =Evaluate(B1) Hit “ok”