# sum numbers in a text+number cell

#### ogg

##### New Member
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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### bryonwoods30

##### Board Regular
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

##### New Member
hi,

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

##### New Member
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

##### Well-known Member
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

##### Board Regular
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

##### Board Regular
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

##### Well-known Member
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

##### Well-known Member
Try in I57,

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

Replies
3
Views
918
Replies
9
Views
495
Replies
14
Views
207
Replies
2
Views
268
Replies
4
Views
337

1,191,517
Messages
5,987,063
Members
440,074
Latest member
Emmanuelian

### 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.

### Which adblocker are you using?

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

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