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