How do I sum numbers from cells that contain text

[Guest]

I would like to combine numbers and text in the same cell, and sum the
numbers at the bottom of a column. Is there a way I can do this? The text
has no numerical value, and I wish the function to ignore it entirely.

Example of what I need:

art show 10
trade show 18
store 14

42 (total of numbers in cells)

Any clues appreciated! -Nick

 

[Bob Phillips]

=SUMPRODUCT(--(MID(A1:A10,FIND("~",SUBSTITUTE(A1:A10,"
","~",LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10," ",""))))+1,99)))

where A1:A10 is the cells in question.

Two points,

each cell must have at least one space before the number
all cells must have data

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Umlas]

Ctrl/Shift/Enter this:
=1*MID(A1,MATCH(FALSE,ISERROR(MID(A1,ROW($1:$100),1)*1),0),100) --gives
10 for the first item.
then fill down, then add the results.

Bob Umlas
Excel MVP

 

[Bob Umlas]

That gave me #Value!

=SUMPRODUCT(--(MID(A1:A10,FIND("~",SUBSTITUTE(A1:A10,"
","~",LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10," ",""))))+1,99)))

where A1:A10 is the cells in question.

Two points,

each cell must have at least one space before the number
all cells must have data

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jason Morin]

Or

=MID(A1,MIN(SEARCH
({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),99)*1

*Not* array-entered. Based on solution by Domenic.

HTH
Jason
Atlanta, GA

 

[Guest]

This almost does it, but it only calculate the first cell in the row. If my
cell range is f34:f44, how should I write it? I tried to CSE this:
=1*MID(F34:f44,MATCH(FALSE,ISERROR(MID(f34:f44,ROW($1:$100),1)*1),0),100)
but it didnt work.

 

[Bob Phillips]

It will if not all cells have valid data, see my two points.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jason Morin]

Bob-

Even if both criteria are met, it still returns #VALUE!.

Jason

 

[Roger H.]

Suggestion: In cell B1 input =VALUE(RIGHT(A1,2)) , copy downward.Then simply
use the SUM Function to get the total for the above celss within Column B :
SUM(B1:B100). I'm going under the assuption that your numbers are confined
to two digits, as you show in your example.

 

[Bob Phillips]

Hi Jason,

This is my data

art show 10
trade show 18
store 14
a 1
a 1
a 1
a 1
A 1
A 1
a 1


and I get 49. Is the NG wrap around losing a space character? If I remove
that space in my formula, I get #VALUE.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

You need to put it in G34 say, and copy down to G44, then add these up.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jason Morin]

Hey (hey = hello in Georgia <g>) Bob-

Weird. I still get an error value, even when I manually
re-create the data in A1:A10.

Regards,
Jason

 

[Aladin Akyurek]

=SUMPRODUCT(--RIGHT(" 0"&A1:A10,LEN(" 0"&A1:A10)-SEARCH("@",SUBSTITUTE("
0"&A1:A10," ","@",LEN(" 0"&A1:A10)-LEN(SUBSTITUTE(" 0"&A1:A10," ",""))))))

should cope with cells whose last word bit is a 'number', which house
true numbers, which are empty.

 

[Bob Phillips]

Excellent. Thank you.

Bob

=SUMPRODUCT(--RIGHT(" 0"&A1:A10,LEN(" 0"&A1:A10)-SEARCH("@",SUBSTITUTE("
0"&A1:A10," ","@",LEN(" 0"&A1:A10)-LEN(SUBSTITUTE(" 0"&A1:A10," ",""))))))

should cope with cells whose last word bit is a 'number', which house
true numbers, which are empty.

 

[Bob Phillips]

Hey Jason<g>,

Shall I send you a workbook?

Bob

 

[Jason Morin]

Nah. Thx.

-----Original Message-----
Hey Jason<g>,

Shall I send you a workbook?

Bob




.

 

[Harlan Grove]

Aladin Akyurek wrote...

=SUMPRODUCT(--RIGHT(" 0"&A1:A10,LEN(" 0"&A1:A10)-SEARCH("@",
SUBSTITUTE(" 0"&A1:A10," ","@",LEN(" 0"&A1:A10)
-LEN(SUBSTITUTE(" 0"&A1:A10," ",""))))))

should cope with cells whose last word bit is a 'number', which house
true numbers, which are empty.

....

On a bit of a tangent, the 2.0 beta for OpenOffice Calc has surpassed
Excel in this regard. OOo Calc can use real regular expressions in the
same function that Excel provides lightweight wild cards. So this can
be done in OOo Calc using

=SUM(VALUE(IF(ISERROR(SEARCH("[0-9]";A1:A5));"0";MID(A1:A5;
SEARCH("[0-9]";A1:A5);32))))

While I understand MSFT staff don't read these newsgroups regularly, I
still hold the dim hope that if enough people ask for a given feature
MSFT may eventually add it. Since VBScript/VJScript have a very good
regexp engine (maybe not as good as Perl's, but quite capable), it's
not as if they'd need to write new code. However, I'm enough of a
realist to expect that flashing text and transparent dialog boxes are
more likely to be added first.