How do I sum numbers from cells that contain text
 

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

=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)


"nicklissa" wrote in message
...
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

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

"nicklissa" wrote in message
...
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

That gave me #Value!

"Bob Phillips" wrote in message
...
=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)


"nicklissa" wrote in message
...
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

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

-----Original Message-----
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

"nicklissa" wrote
in message
news:1F6E39CC-50F2-43AA-A8B1-
...
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


.

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 Umlas" wrote:

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

"nicklissa" wrote in message
...
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

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

--

HTH

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


"Bob Umlas" wrote in message
...
That gave me #Value!

"Bob Phillips" wrote in message
...
=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)


"nicklissa" wrote in message
...
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-

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

Jason

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

--

HTH

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


"Bob Umlas" wrote in message
...
That gave me #Value!

"Bob Phillips"
wrote in message
...
=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)


"nicklissa"
wrote in message
news:1F6E39CC-50F2-43AA-A8B1-
...
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






.

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.
"nicklissa" wrote in message
...
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

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)


"Jason Morin" wrote in message
...
Bob-

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

Jason

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

--

HTH

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


"Bob Umlas" wrote in message
...
That gave me #Value!

"Bob Phillips"
wrote in message
...
=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)


"nicklissa"
wrote in message
news:1F6E39CC-50F2-43AA-A8B1-
...
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






.

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)


"nicklissa" wrote in message
...
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 Umlas" wrote:

Ctrl/Shift/Enter this:

ID(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

"nicklissa" wrote in message
...
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

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


-----Original Message-----
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)


"Jason Morin" wrote
in message
...
Bob-

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

Jason

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

--

HTH

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


"Bob Umlas" wrote in message
...
That gave me #Value!

"Bob Phillips"
wrote in message
...
=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)


"nicklissa"
wrote in message
news:1F6E39CC-50F2-43AA-A8B1-
...
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






.



.

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

Jason Morin wrote:
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



-----Original Message-----
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)


"Jason Morin" wrote

in message

...

Bob-

Even if both criteria are met, it still returns

#VALUE!.

Jason


-----Original Message-----
It will if not all cells have valid data, see my two

points.

--

HTH

RP
(remove nothere from the email address if mailing

direct)


"Bob Umlas" wrote in message
.. .

That gave me #Value!

"Bob Phillips"

wrote in message

. ..

=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)


"nicklissa"

wrote in message

news:1F6E39CC-50F2-43AA-A8B1-

...

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




.



.

Excellent. Thank you.

Bob


"Aladin Akyurek" wrote in message
...
=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.

Jason Morin wrote:
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



-----Original Message-----
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)


"Jason Morin" wrote

in message

...

Bob-

Even if both criteria are met, it still returns

#VALUE!.

Jason


-----Original Message-----
It will if not all cells have valid data, see my two

points.

--

HTH

RP
(remove nothere from the email address if mailing

direct)


"Bob Umlas" wrote in message
.. .

That gave me #Value!

"Bob Phillips"

wrote in message

. ..

=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)


"nicklissa"

wrote in message

news:1F6E39CC-50F2-43AA-A8B1-

...

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




.



.

Hey Jason<g,

Shall I send you a workbook?

Bob

"Jason Morin" wrote in message
...
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

Nah. Thx.

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

Shall I send you a workbook?

Bob

"Jason Morin" wrote
in message
...
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 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.