Calculate points

[Rudi]

This is a test sentence. (500 Points) The idea is to extract the points values from the cell and calculate the total. (800 Points) My question is; can it be done? And how? (500 Points)

The bolded sentence above is in Cell A1. In cell B1, I'd like to enter a formula to calculate the "points" value of all the values in brackets. How would I go about this. Any help is appreciated. TX

PS: The value in the above will calculate to: 1800. :)

[HansV]

I'd put the text and the points in separate cells. But if you really want to use a single cell, create this custom function in a module:

Code: Select all

Function GetPoints(varValue) As Double
  Dim intPos1 As Integer
  intPos1 = InStr(varValue, "(")
  Do While intPos1 > 0
    GetPoints = GetPoints + Val(Mid(varValue, intPos1 + 1))
    intPos1 = InStr(intPos1 + 1, varValue, "(")
  Loop
End Function

In cell B1, enter the formula

=GetPoints(A1)

[Rudi]

I'd agree, but this is a clients request and I have no control over their spreadsheets.

Many TX for the custom function. Appreciate it stax!