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.

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.

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.

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.

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.

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.

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.

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.