Sum values in string in a cell
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
"Rick Rothstein" wrote:
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
|