Good method to parse value from PowerPivot PivotItem?

Question

I have a PivotItem with the following .name and .value properties:

[dimCalendar].[MonthName].&[April 2013]

I am only interested in the part where it says April 2013.

What is a good method to parse or otherwise get this value from the PivotItem?

Answer 1

Solution one: Split and replace

Split would be best with [ as the delimiter character. Get the upper bound of the Split array and replace the trailing ] with "".

Solution two: regular expressions

Add Microsoft VBregularexpression 5.5 as a reference.

Private Function Method(str As String) As String
Dim regexp As New regexp
regexp.Pattern = "\[.*\]\[.*\]\[(.*)\]"
Method = regexp.Replace(str, "$1")
End Function

Answer 2

If you only need the last one, this will work nicely :

Sub test_user1283776()
MsgBox get_PivotItem_From_PowerPivot("[dimCalendar].[MonthName].&[April 2013]")
End Sub

Function get_PivotItem_From_PowerPivot(PowerString As String) As String
    Dim A() As String
    A = Split(PowerString, ".&[")
    get_PivotItem_From_PowerPivot = Trim(Replace(A(UBound(A)), "]", ""))
End Function