calculate a cell with letters and numbers in it.

[2k05gt]

= SUM(B2, B12, B21, B32, B40, B49, B52, B57, B73, B83, B87, B104, B114, B118, B124, B128, B142, B149, B162)

each cell looks like ## interfaces
I need a total of all ## and exclude the text. Can this be done?

 

[barry houdini]

A bit difficult to do with a single formula

Is it possible to change your data so that each cell contains just a number but you custom format as

0 "interfaces"

so that they display as ## interfaces

you can then just use your SUM formula to sum the values

 

[2k05gt]

I tried using countif (Left, 2) but am having trouble with this as well
I can't change the cells since the data is updated from a flat file.

 

[barry houdini]

How many digits will the numbers have?

If there is a space between the number and "interfaces" then you can use this formula to extract the number, whatever the number of digits

=LEFT(B2,FIND(" ",B2)-1)+0

you could put this formula in another column and then sum that......

 

[barry houdini]

Another option....

what's in the intermediate cells, B3, B4 etc.?

Assuming these won't contain the word "interfaces" then perhaps you can use this formula

=SUM(IF(RIGHT(B2:B162,10)="interfaces",LEFT(B2:B162,FIND(" ",B2:B162)-1)+0))

confirmed with CTRL+SHIFT+ENTER

 

[2k05gt]

Thanks, That was it...