Sum values in string in a cell

[Francis]

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

 

[Jacob Skaria]

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

 

[Francis]

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]

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

 

[Harlan Grove]

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,12),
FIND(":",MID(x&":",SMALL(IF(MID(x,seq,1)="$",seq),seq)+1,12))-1)))

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

 

[Francis]

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

 

[Rick Rothstein]

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

 

[Ron Rosenfeld]

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

 

[Francis]

Hi Rick

The 2nd UDF doesn't work as expected, it return the value for the first
amount
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]

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

 

[Rick Rothstein]

Sorry, my mistake, try these instead...

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, "INC")
For X = 0 To UBound(Parts)
AddMoney = AddMoney + Val(Replace(Replace(Parts(X), "$", ""), ",", ""))
Next
End Function

 

[Harlan Grove]

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]"),",",""))

 

[Ron Rosenfeld]

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