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

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

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






.

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






.

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

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

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

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