Help me! Adding number/text combos

[Holly]

How can I sum up a combination of numbers and text in
Excel 97? For example, if I have cells that say "2 dogs"
and "4 dogs," is there a way to make the SUM function
display "6 dogs"?

 

[Harlan Grove]

How can I sum up a combination of numbers and text in
Excel 97? For example, if I have cells that say "2 dogs"
and "4 dogs," is there a way to make the SUM function
display "6 dogs"?

Not with SUM alone. If you had a range named List containing the text entries

6 hamsters
1 dogs
6 dogs
2 dogs
2 cats
2 birds
5 dogs
2 turtles
4 birds
2 birds
1 hamsters
2 birds
6 birds
4 birds
2 cats
4 frogs
4 cats
4 frogs
2 frogs
4 cats

and you entered 'dogs' (without the single quotes) in a cell named A, you could
get your desired result using the array formula

=SUM(IF(SUBSTITUTE(List,A,"")<>List,--SUBSTITUTE(List,A,"")))&" "&A