Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
=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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 . |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 . |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 . |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
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 . . |
#13
|
|||
|
|||
=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 . . |
#14
|
|||
|
|||
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 . . |
#15
|
|||
|
|||
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 |
#16
|
|||
|
|||
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 . |
#17
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort or Filter option? | Excel Worksheet Functions | |||
Converting Numbers to Text properly | Excel Discussion (Misc queries) | |||
Convert data of cells to any type: Number, Date&Time, Text | Excel Discussion (Misc queries) | |||
Sorting when some numbers have a text suffix | Excel Discussion (Misc queries) | |||
Adding cells with numbers and text | Excel Worksheet Functions |