sum numbers in a text+number cell

[ogg]

hi all

i'm having a small problem

i have many cells in my sheet with this text :

"free space in this rack : 19 U"
"free space in this rack : 7 U"
"free space in this rack : 0 U"

etc...

and i wanna have a cell with the sum of all the numbers like this :

"total space in all racks : XX U"

(or "total space in all racks (in U) : " and another cell with the result "XX" if its more simple)

i don't know anything about excel formulas, i tried some small things like SUM, SUMIF, RIGHT etc.. but can't manage to do it.

is there a way of doing it easily ?

cells are C47 to Q47 (and i'll put the total in I57 for example)
in fact i just wanna sum the numbers (and forget the text), so maybe there's a simple command to "ignore" the text ?

regards,

thanks

 

[bryonwoods30]

i found this formula for you and it works like a charm, exactly what you need.
to get it to work you need to do the following

in cell c48 (below your first string in c47 paste this formula in there
=1*MID(C47,MATCH(TRUE,ISNUMBER(1*MID(C47,ROW($1:$40),1)),0),COUNT(1*MID(C47,ROW($1:$40),1)))

now when you hit enter it will say #N/A, double click on the cell so that the formula shows up again. push control + shift + enter and you will get the number value extracted from c47.
then just drag the formula to the right to q48 and it will extract all your number values, then just sum up that row

 

[ogg]

hi,

thanks for your fast answer

the 1st part is working (the formula gets me the value of c47)

but the second part is not working. (or i dunno how to "drag" the formula to the right.

my cells are c47, e47, g47, i47, k47, m47, o47, q47
and your formula is in c4 (below the c47 cell), i can put anywhere it works.. but how to "drag" your formula ? (to the right, it's empty cells)

 

[ogg]

oh i see now.. the results display all at the bottom of each cell, that's cool

but they overwrite my other cells (or i need to add some rows, and after that, SUM them all to get the result, and find a way to hide them)

thx for your help, but maybe there's a simple way of doing it ?
otherwise, i'll choose your solution, thx !

 

[texasalynn]

when you put your cursor in the cell with the formula you will be a black box in the lower right hand corner. Left Click on mouse, that box, and hold to drag across to the cell you want to end with.

 

[bryonwoods30]

since there is empty cells between them. just control c to copy the cell and then control v to paste in the cells you want it in.

to drag a formula, highlight c47, then move your mouse to the lower right hand corner of the cell until you get a + sign. left click and hold the mouse down as you drag to the right.

 

[bryonwoods30]

yeah you can insert a row, put the formulas in there and then do the sum function in the cell that you want to display the sum. then just hide the row you inserted.

 

[jasonb75]

Assuming that all of those cells hold the same text string

free space in this rack : xx U

Confirm with Shift Ctrl Enter

=SUM(IF(ISODD(COLUMN(C47:Q47)),(--SUBSTITUTE(SUBSTITUTE(C47:Q47," U",),"free space in this rack : ",))))

Note that the text string is case sensetive.

 

[Haseeb Avarakkan]

Try in I57,

=SUMPRODUCT(--(0&TRIM(SUBSTITUTE(UPPER(MID(C47:Q47,FIND(":",C47:Q47&":")+1,250)),"U",""))))