sum numbers in a text+number cell

ogg

New Member
Joined
Mar 10, 2011
Messages
3
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
 
Last edited:

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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
 
Upvote 0
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)
 
Upvote 0
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 !
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Try in I57,

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

Forum statistics

Threads
1,222,072
Messages
6,163,746
Members
451,855
Latest member
mcook36155

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top