| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum / count data from text string with delimiter
I would like to be able to sum numbers from a segment of a text string. The
cells of data are in a row. The data looks like the following: 1x1.5 21x9 3x2 In one formula I need to sum the numbers prior to the "x". In a different formula I need to sum the numbers after the "x". The number of digits is not a fixed length but the "x" consistently delimits the numbers. In addition I need to count the number of unique values prior to the "x". I've searched for the method to do this but I can not fine how. I would appreciate any help on this. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum / count data from text string with delimiter
With
A1:A10 containing values of the form "numberXnumber" or blanks (no regular text) Try something like this: The sum of the values preceding the "X" =SUMPRODUCT(--TRIM("0"&LEFT(A1:A10&"0",SEARCH("X",A1:A10&"X")-1))) The sum of the values following the "X" =SUMPRODUCT(--TRIM("0"&MID(A1:A10,SEARCH("X",A1:A10&"X")+1,255)) ) The count of unique values preceding the "X" =SUM(N(FREQUENCY(--TRIM("0"&LEFT(A1:A10&"0",SEARCH("X",A1:A10&"X")-1)),--TRIM("0"&LEFT(A1:A10&"0",SEARCH("X",A1:A10&"X")-1)))0))-(COUNTBLANK(A1:A10)0) Does that help? *********** Regards, Ron XL2002, WinXP "J" wrote: I would like to be able to sum numbers from a segment of a text string. The cells of data are in a row. The data looks like the following: 1x1.5 21x9 3x2 In one formula I need to sum the numbers prior to the "x". In a different formula I need to sum the numbers after the "x". The number of digits is not a fixed length but the "x" consistently delimits the numbers. In addition I need to count the number of unique values prior to the "x". I've searched for the method to do this but I can not fine how. I would appreciate any help on this. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum / count data from text string with delimiter
Thanks Ron! I'll give it a try in just a little while.
"Ron Coderre" wrote: With A1:A10 containing values of the form "numberXnumber" or blanks (no regular text) Try something like this: The sum of the values preceding the "X" =SUMPRODUCT(--TRIM("0"&LEFT(A1:A10&"0",SEARCH("X",A1:A10&"X")-1))) The sum of the values following the "X" =SUMPRODUCT(--TRIM("0"&MID(A1:A10,SEARCH("X",A1:A10&"X")+1,255)) ) The count of unique values preceding the "X" =SUM(N(FREQUENCY(--TRIM("0"&LEFT(A1:A10&"0",SEARCH("X",A1:A10&"X")-1)),--TRIM("0"&LEFT(A1:A10&"0",SEARCH("X",A1:A10&"X")-1)))0))-(COUNTBLANK(A1:A10)0) Does that help? *********** Regards, Ron XL2002, WinXP "J" wrote: I would like to be able to sum numbers from a segment of a text string. The cells of data are in a row. The data looks like the following: 1x1.5 21x9 3x2 In one formula I need to sum the numbers prior to the "x". In a different formula I need to sum the numbers after the "x". The number of digits is not a fixed length but the "x" consistently delimits the numbers. In addition I need to count the number of unique values prior to the "x". I've searched for the method to do this but I can not fine how. I would appreciate any help on this. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum / count data from text string with delimiter
Fatastic! It works great. Thanks for the help Ron.
"Ron Coderre" wrote: With A1:A10 containing values of the form "numberXnumber" or blanks (no regular text) Try something like this: The sum of the values preceding the "X" =SUMPRODUCT(--TRIM("0"&LEFT(A1:A10&"0",SEARCH("X",A1:A10&"X")-1))) The sum of the values following the "X" =SUMPRODUCT(--TRIM("0"&MID(A1:A10,SEARCH("X",A1:A10&"X")+1,255)) ) The count of unique values preceding the "X" =SUM(N(FREQUENCY(--TRIM("0"&LEFT(A1:A10&"0",SEARCH("X",A1:A10&"X")-1)),--TRIM("0"&LEFT(A1:A10&"0",SEARCH("X",A1:A10&"X")-1)))0))-(COUNTBLANK(A1:A10)0) Does that help? *********** Regards, Ron XL2002, WinXP "J" wrote: I would like to be able to sum numbers from a segment of a text string. The cells of data are in a row. The data looks like the following: 1x1.5 21x9 3x2 In one formula I need to sum the numbers prior to the "x". In a different formula I need to sum the numbers after the "x". The number of digits is not a fixed length but the "x" consistently delimits the numbers. In addition I need to count the number of unique values prior to the "x". I've searched for the method to do this but I can not fine how. I would appreciate any help on this. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum / count data from text string with delimiter
You're very welcome, J....I'm glad I could help.
*********** Regards, Ron XL2002, WinXP "J" wrote: Fatastic! It works great. Thanks for the help Ron. "Ron Coderre" wrote: With A1:A10 containing values of the form "numberXnumber" or blanks (no regular text) Try something like this: The sum of the values preceding the "X" =SUMPRODUCT(--TRIM("0"&LEFT(A1:A10&"0",SEARCH("X",A1:A10&"X")-1))) The sum of the values following the "X" =SUMPRODUCT(--TRIM("0"&MID(A1:A10,SEARCH("X",A1:A10&"X")+1,255)) ) The count of unique values preceding the "X" =SUM(N(FREQUENCY(--TRIM("0"&LEFT(A1:A10&"0",SEARCH("X",A1:A10&"X")-1)),--TRIM("0"&LEFT(A1:A10&"0",SEARCH("X",A1:A10&"X")-1)))0))-(COUNTBLANK(A1:A10)0) Does that help? *********** Regards, Ron XL2002, WinXP "J" wrote: I would like to be able to sum numbers from a segment of a text string. The cells of data are in a row. The data looks like the following: 1x1.5 21x9 3x2 In one formula I need to sum the numbers prior to the "x". In a different formula I need to sum the numbers after the "x". The number of digits is not a fixed length but the "x" consistently delimits the numbers. In addition I need to count the number of unique values prior to the "x". I've searched for the method to do this but I can not fine how. I would appreciate any help on this. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum / count data from text string with delimiter
To extract the part before the x: =LEFT(A1,FIND("x",A1)-1)
To extract the part after the x: =RIGHT(A1,LEN(A1)-FIND("x",A1)) -- David Biddulph "J" wrote in message ... I would like to be able to sum numbers from a segment of a text string. The cells of data are in a row. The data looks like the following: 1x1.5 21x9 3x2 In one formula I need to sum the numbers prior to the "x". In a different formula I need to sum the numbers after the "x". The number of digits is not a fixed length but the "x" consistently delimits the numbers. In addition I need to count the number of unique values prior to the "x". I've searched for the method to do this but I can not fine how. I would appreciate any help on this. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum / count data from text string with delimiter
Thanks David! This will come in handy.
"David Biddulph" wrote: To extract the part before the x: =LEFT(A1,FIND("x",A1)-1) To extract the part after the x: =RIGHT(A1,LEN(A1)-FIND("x",A1)) -- David Biddulph "J" wrote in message ... I would like to be able to sum numbers from a segment of a text string. The cells of data are in a row. The data looks like the following: 1x1.5 21x9 3x2 In one formula I need to sum the numbers prior to the "x". In a different formula I need to sum the numbers after the "x". The number of digits is not a fixed length but the "x" consistently delimits the numbers. In addition I need to count the number of unique values prior to the "x". I've searched for the method to do this but I can not fine how. I would appreciate any help on this. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum / count data from text string with delimiter
One more twist. I need to be able to sum numbers after the x provided the
number before the x = 1. If cells A1:Z1 have the following type of information: 1x1.5 21x9 3x2 (Some of these cells are null) In cell AA1, sum the values to the right of the x whose number to the left of the x is 1, in cell AB1 sum the values to the right of the x whose number to the left of the x is 2... Thanks again for the great help. "David Biddulph" wrote: To extract the part before the x: =LEFT(A1,FIND("x",A1)-1) To extract the part after the x: =RIGHT(A1,LEN(A1)-FIND("x",A1)) -- David Biddulph "J" wrote in message ... I would like to be able to sum numbers from a segment of a text string. The cells of data are in a row. The data looks like the following: 1x1.5 21x9 3x2 In one formula I need to sum the numbers prior to the "x". In a different formula I need to sum the numbers after the "x". The number of digits is not a fixed length but the "x" consistently delimits the numbers. In addition I need to count the number of unique values prior to the "x". I've searched for the method to do this but I can not fine how. I would appreciate any help on this. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I change the DEFAULT delimiter for pasting text data? | Excel Discussion (Misc queries) | |||
| my pivot table does not count all intances of a text string | Excel Discussion (Misc queries) | |||
| Locate and count the recurrences of a text string | Excel Discussion (Misc queries) | |||
| find and remove a string of a cell value with comma as delimiter | Excel Discussion (Misc queries) | |||
| how to count the nr of occurrences of a text string in a cell rang | Excel Worksheet Functions | |||
Hybrid Mode
