A2 contain the following string :

** CLAIMS ** YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC $30,708.72: YR2008
NO. 2 INC $13,922.14

I want in B2 to add up the No which will give 5 on the above example
and in C2 give me the total amount of 45590.68

Is there a way to do this?

TIA


--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

Dear Francis

Suppose you have the below data in cell A1 (as a single line)
** CLAIMS ** YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC $30,708.72: YR2008
NO. 2 INC $13,922.14

Try this UDF which will give you the total..

=getvaluefromstring(A1)


Launch VBE using Alt+F11. Insert a module and save..

Function GetValuefromString(varTemp As Range) As Currency
Dim intStart As Integer
Dim intPos As Integer
Dim strData As String
strData = varTemp.Text & ":"
intStart = 1
Do
intPos = InStr(intStart, strData, "INC")
If intPos < 0 Then
GetValuefromString = GetValuefromString + Trim(Mid(strData, _
intPos + 3, InStr(intPos, strData, ":") - intPos - 3))
End If
intStart = intPos + 1
Loop While intPos < 0
End Function




If this post helps click Yes
---------------
Jacob Skaria


"Francis" wrote:

A2 contain the following string :

** CLAIMS ** YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC $30,708.72: YR2008
NO. 2 INC $13,922.14

I want in B2 to add up the No which will give 5 on the above example
and in C2 give me the total amount of 45590.68

Is there a way to do this?

TIA


--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

Hi Jacob

Thanks! Your UDF does return the total for the amount. How do I modify for
the NO.
which will give me 5 in my example.

Is there no formula that can do these? I am looking a formulas that can do
this if there are.
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Jacob Skaria" wrote:

Dear Francis

Suppose you have the below data in cell A1 (as a single line)
** CLAIMS ** YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC $30,708.72: YR2008
NO. 2 INC $13,922.14

Try this UDF which will give you the total..

=getvaluefromstring(A1)


Launch VBE using Alt+F11. Insert a module and save..

Function GetValuefromString(varTemp As Range) As Currency
Dim intStart As Integer
Dim intPos As Integer
Dim strData As String
strData = varTemp.Text & ":"
intStart = 1
Do
intPos = InStr(intStart, strData, "INC")
If intPos < 0 Then
GetValuefromString = GetValuefromString + Trim(Mid(strData, _
intPos + 3, InStr(intPos, strData, ":") - intPos - 3))
End If
intStart = intPos + 1
Loop While intPos < 0
End Function




If this post helps click Yes
---------------
Jacob Skaria


"Francis" wrote:

A2 contain the following string :

** CLAIMS ** YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC $30,708.72: YR2008
NO. 2 INC $13,922.14

I want in B2 to add up the No which will give 5 on the above example
and in C2 give me the total amount of 45590.68

Is there a way to do this?

TIA


--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

I assume the string can very in length. A formula would be difficult. If you
would really like to have the UDF amended to return the total numbers let me
know...

If this post helps click Yes
---------------
Jacob Skaria


"Francis" wrote:

Hi Jacob

Thanks! Your UDF does return the total for the amount. How do I modify for
the NO.
which will give me 5 in my example.

Is there no formula that can do these? I am looking a formulas that can do
this if there are.
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Jacob Skaria" wrote:

Dear Francis

Suppose you have the below data in cell A1 (as a single line)
** CLAIMS ** YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC $30,708.72: YR2008
NO. 2 INC $13,922.14

Try this UDF which will give you the total..

=getvaluefromstring(A1)


Launch VBE using Alt+F11. Insert a module and save..

Function GetValuefromString(varTemp As Range) As Currency
Dim intStart As Integer
Dim intPos As Integer
Dim strData As String
strData = varTemp.Text & ":"
intStart = 1
Do
intPos = InStr(intStart, strData, "INC")
If intPos < 0 Then
GetValuefromString = GetValuefromString + Trim(Mid(strData, _
intPos + 3, InStr(intPos, strData, ":") - intPos - 3))
End If
intStart = intPos + 1
Loop While intPos < 0
End Function




If this post helps click Yes
---------------
Jacob Skaria


"Francis" wrote:

A2 contain the following string :

** CLAIMS ** YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC $30,708.72: YR2008
NO. 2 INC $13,922.14

I want in B2 to add up the No which will give 5 on the above example
and in C2 give me the total amount of 45590.68

Is there a way to do this?

TIA


--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

Thanks. Agreed that a formula would be difficult in this case.
Appreciate if you can modify the UDF for total on NO.


--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Jacob Skaria" wrote:

I assume the string can very in length. A formula would be difficult. If you
would really like to have the UDF amended to return the total numbers let me
know...

If this post helps click Yes
---------------
Jacob Skaria


"Francis" wrote:

Hi Jacob

Thanks! Your UDF does return the total for the amount. How do I modify for
the NO.
which will give me 5 in my example.

Is there no formula that can do these? I am looking a formulas that can do
this if there are.
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Jacob Skaria" wrote:

Dear Francis

Suppose you have the below data in cell A1 (as a single line)
** CLAIMS ** YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC $30,708.72: YR2008
NO. 2 INC $13,922.14

Try this UDF which will give you the total..

=getvaluefromstring(A1)


Launch VBE using Alt+F11. Insert a module and save..

Function GetValuefromString(varTemp As Range) As Currency
Dim intStart As Integer
Dim intPos As Integer
Dim strData As String
strData = varTemp.Text & ":"
intStart = 1
Do
intPos = InStr(intStart, strData, "INC")
If intPos < 0 Then
GetValuefromString = GetValuefromString + Trim(Mid(strData, _
intPos + 3, InStr(intPos, strData, ":") - intPos - 3))
End If
intStart = intPos + 1
Loop While intPos < 0
End Function




If this post helps click Yes
---------------
Jacob Skaria


"Francis" wrote:

A2 contain the following string :

** CLAIMS ** YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC $30,708.72: YR2008
NO. 2 INC $13,922.14

I want in B2 to add up the No which will give 5 on the above example
and in C2 give me the total amount of 45590.68

Is there a way to do this?

TIA


--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

Here is my take on a UDF solution; the first UDF gives the sum of the "NO."
values and the second gives the sum of the money values...

Function AddNo(S As String) As Double
Dim X As Long
Dim Parts() As String
Parts = Split(S, "NO.")
For X = 0 To UBound(Parts)
AddNo = AddNo + Val(Parts(X))
Next
End Function

Function AddMoney(S As String) As Double
Dim X As Long
Dim Parts() As String
Parts = Split(S, "No.")
For X = 0 To UBound(Parts)
AddMoney = AddMoney + Val(Replace(Split(Parts(X), "INC")(1), "$", ""))
Next
End Function

--
Rick (MVP - Excel)


"Francis" <xlsmate(AT)gmail(DOT)com wrote in message
...
Thanks. Agreed that a formula would be difficult in this case.
Appreciate if you can modify the UDF for total on NO.


--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Jacob Skaria" wrote:

I assume the string can very in length. A formula would be difficult. If
you
would really like to have the UDF amended to return the total numbers let
me
know...

If this post helps click Yes
---------------
Jacob Skaria


"Francis" wrote:

Hi Jacob

Thanks! Your UDF does return the total for the amount. How do I modify
for
the NO.
which will give me 5 in my example.

Is there no formula that can do these? I am looking a formulas that can
do
this if there are.
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have
asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Jacob Skaria" wrote:

Dear Francis

Suppose you have the below data in cell A1 (as a single line)
** CLAIMS ** YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC $30,708.72:
YR2008
NO. 2 INC $13,922.14

Try this UDF which will give you the total..

=getvaluefromstring(A1)


Launch VBE using Alt+F11. Insert a module and save..

Function GetValuefromString(varTemp As Range) As Currency
Dim intStart As Integer
Dim intPos As Integer
Dim strData As String
strData = varTemp.Text & ":"
intStart = 1
Do
intPos = InStr(intStart, strData, "INC")
If intPos < 0 Then
GetValuefromString = GetValuefromString + Trim(Mid(strData, _
intPos + 3, InStr(intPos, strData, ":") - intPos - 3))
End If
intStart = intPos + 1
Loop While intPos < 0
End Function




If this post helps click Yes
---------------
Jacob Skaria


"Francis" wrote:

A2 contain the following string :

** CLAIMS ** YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC
$30,708.72: YR2008
NO. 2 INC $13,922.14

I want in B2 to add up the No which will give 5 on the above
example
and in C2 give me the total amount of 45590.68

Is there a way to do this?

TIA


--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have
asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

Francis <xlsmate(AT)gmail(DOT)com wrote...
A2 contain the following string :

** CLAIMS ** *YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC *$30,708.72: YR2008
NO. 2 INC *$13,922.14

I want in B2 to add up the No which will give 5 on the above example
and in C2 *give me the total amount of 45590.68

Is there a way to do this?
....

Like it or not, the best way to handle this sort of thing would be to
parse such strings (records) into multiple columns (fields), then sum
the numbers.

You could parse and sum in single formulas, but it's nowhere near
simple and very inefficient. Here's one way. Define a name like seq
referring to the formula

=ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,255,1))

Then if your string above were in a cell named x, use the array
formula

=SUM(--IF(seq<=(LEN(x)-LEN(SUBSTITUTE(x,"NO. ","")))/4,
LEFT(MID(x,SMALL(IF(MID(x,seq,4)="NO. ",seq),seq)+4,6),
FIND(" ",MID(x&" ",SMALL(IF(MID(x,seq,4)="NO. ",seq),seq)+4,6)))))

to sum the numbers following the substring "NO. " in your full string/
record, and use the array formula

=SUM(--IF(seq<=LEN(x)-LEN(SUBSTITUTE(x,"$","")),
LEFT(MID(x,SMALL(IF(MID(x,seq,1)="$",seq),seq)+1,1 2),
FIND(":",MID(x&":",SMALL(IF(MID(x,seq,1)="$",seq), seq)+1,12))-1)))

to sum the amounts following the dollar signs ($).

Hi
Thanks.
Agreed that the best is to parse into multiple columns and then do
the sum.
The solutions provided works. I was thinking that formula can't
handle these but you have proved me wrong.
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Harlan Grove" wrote:

Francis <xlsmate(AT)gmail(DOT)com wrote...
A2 contain the following string :

** CLAIMS ** YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC $30,708.72: YR2008
NO. 2 INC $13,922.14

I want in B2 to add up the No which will give 5 on the above example
and in C2 give me the total amount of 45590.68

Is there a way to do this?
....

Like it or not, the best way to handle this sort of thing would be to
parse such strings (records) into multiple columns (fields), then sum
the numbers.

You could parse and sum in single formulas, but it's nowhere near
simple and very inefficient. Here's one way. Define a name like seq
referring to the formula

=ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,255,1))

Then if your string above were in a cell named x, use the array
formula

=SUM(--IF(seq<=(LEN(x)-LEN(SUBSTITUTE(x,"NO. ","")))/4,
LEFT(MID(x,SMALL(IF(MID(x,seq,4)="NO. ",seq),seq)+4,6),
FIND(" ",MID(x&" ",SMALL(IF(MID(x,seq,4)="NO. ",seq),seq)+4,6)))))

to sum the numbers following the substring "NO. " in your full string/
record, and use the array formula

=SUM(--IF(seq<=LEN(x)-LEN(SUBSTITUTE(x,"$","")),
LEFT(MID(x,SMALL(IF(MID(x,seq,1)="$",seq),seq)+1,1 2),
FIND(":",MID(x&":",SMALL(IF(MID(x,seq,1)="$",seq), seq)+1,12))-1)))

to sum the amounts following the dollar signs ($).

On Fri, 8 May 2009 03:45:01 -0700, Francis <xlsmate(AT)gmail(DOT)com wrote:

A2 contain the following string :

** CLAIMS ** YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC $30,708.72: YR2008
NO. 2 INC $13,922.14

I want in B2 to add up the No which will give 5 on the above example
and in C2 give me the total amount of 45590.68

Is there a way to do this?

TIA

Harlan's answer, with regard to separate columns for the values, will be the
easiest to maintain.

If you must keep it together in a single string, you could download and install
Longre's free morefunc.xll add-in (Google to find a good download site), and
then use these **array-entered** formulas:

Sum of NO:

=EVAL(MCONCAT(--REGEX.MID(A1,"(?<=NO\.)\s*[-+]?\b[0-9,]*\.?[0-9]+\b",
INTVECTOR(REGEX.COUNT(
A1,"(?<=NO\.)\s*[-+]?\b[0-9,]*\.?[0-9]+\b",FALSE),1),FALSE),"+"))

Sum of $:

=EVAL(MCONCAT(--REGEX.MID(A1,"(?<=\$)[-+]?\b[0-9,]*\.?[0-9]+\b",
INTVECTOR(REGEX.COUNT(
A1,"(?<=NO\.)\s*[-+]?\b[0-9,]*\.?[0-9]+\b",FALSE),1),FALSE),"+"))

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.
--ron

Ron Rosenfeld wrote...
....
Sum of NO:

=EVAL(MCONCAT(--REGEX.MID(A1,"(?<=NO\.)\s*[-+]?\b[0-9,]*\.?[0-9]+\b",
INTVECTOR(REGEX.COUNT(A1,"(?<=NO\.)\s*[-+]?\b[0-9,]*\.?[0-9]+\b",
FALSE),1),FALSE),"+"))

I had wanted to avoid regexps, but if you're going to use 'em, use 'em
efficiently. Either

=SUMPRODUCT(--REGEX.MID(x,"(?<=NO\. )\d+",INTVECTOR(REGEX.COUNT(x,"NO
\. "),1)))

or

=EVAL(REGEX.SUBSTITUTE(x&"NO. 0",".*?NO\. (\d+)","+[1]"))

Sum of $:

=EVAL(MCONCAT(--REGEX.MID(A1,"(?<=\$)[-+]?\b[0-9,]*\.?[0-9]+\b",
INTVECTOR(REGEX.COUNT(A1,"(?<=NO\.)\s*[-+]?\b[0-9,]*\.?[0-9]+\b",
FALSE),1),FALSE),"+"))

Either

=SUMPRODUCT(--REGEX.MID(x&":","(?<=\$)[0-9,.]+(?=:)",
INTVECTOR(REGEX.COUNT(x&":","\$[0-9,.]+:"),1)))

or

=EVAL(SUBSTITUTE(REGEX.SUBSTITUTE(x&":",".*?\$
([0-9,.]+):","+[1]"),",",""))

On Fri, 8 May 2009 14:10:59 -0700 (PDT), Harlan Grove
wrote:

Ron Rosenfeld wrote...
...
Sum of NO:

=EVAL(MCONCAT(--REGEX.MID(A1,"(?<=NO\.)\s*[-+]?\b[0-9,]*\.?[0-9]+\b",
INTVECTOR(REGEX.COUNT(A1,"(?<=NO\.)\s*[-+]?\b[0-9,]*\.?[0-9]+\b",
FALSE),1),FALSE),"+"))

I had wanted to avoid regexps, but if you're going to use 'em, use 'em
efficiently. Either

=SUMPRODUCT(--REGEX.MID(x,"(?<=NO\. )\d+",INTVECTOR(REGEX.COUNT(x,"NO
\. "),1)))

or

=EVAL(REGEX.SUBSTITUTE(x&"NO. 0",".*?NO\. (\d+)","+[1]"))

Sum of $:

=EVAL(MCONCAT(--REGEX.MID(A1,"(?<=\$)[-+]?\b[0-9,]*\.?[0-9]+\b",
INTVECTOR(REGEX.COUNT(A1,"(?<=NO\.)\s*[-+]?\b[0-9,]*\.?[0-9]+\b",
FALSE),1),FALSE),"+"))

Either

=SUMPRODUCT(--REGEX.MID(x&":","(?<=\$)[0-9,.]+(?=:)",
INTVECTOR(REGEX.COUNT(x&":","\$[0-9,.]+:"),1)))

or

=EVAL(SUBSTITUTE(REGEX.SUBSTITUTE(x&":",".*?\$
([0-9,.]+):","+[1]"),",",""))

Much better. Thanks.
--ron