help, create a new column in Powerpivot that pulls out numbers from a string

[MartinL]

Hi everyone

I have a sheet that uses a UDF to get the weight from a product string

Function Wgt(rCell As Range) As Double
'***************************************************************
'*Extract the last set of numbers from a cell containing text and numbers.
'*i.e. Cooking Onions 500G (C) = 500
'*i.e. Finest Sweet Onions 3 Pack = 0
'*i.e. Pink Curry Onions 1.2Kg = 1200'***************************************************************
    
Dim iCount As Integer, i As Integer, iLoop As Integer
    Dim sText As String
    Dim lNum As String
    Dim vVal
    Dim Flag As Boolean
    
     
     'get the Alphanumeric value of the cell
    sText = rCell
    
    'determine the number of characters in the cell
    iLoop = Len(sText)
        
            'Set up the counter to loop backwards through the length of the cell
            For iCount = 1 To iLoop
            
            'Put the value of the character determined by the counter into vVal
            vVal = Mid(sText, iCount, 1)
            
                'If vVal = "X" Then Flag = True
                
                'If Flag = True Then _
                'If vVal is a number add 1 to i and put the number into lNum
                If IsNumeric(vVal) Then
                    i = i + 1
                    lNum = lNum & Mid(sText, iCount, 1)
                    
                End If
                'End If
                 
                'If you have collected some numbers but now there is some text (ie "x") stop.
                If i > 0 And IsNumeric(vVal) = False Then GoTo Finish
            Next iCount
        
     
Finish:
    'put the value of lNum into the cell
    If lNum = "" Then lNum = 0
    If lNum < 10 Then lNum = 0
    Wgt = CDbl(lNum)
End Function

However I am now using powerpivot and I need to get this value into a new column with powerpivot, unless somone can suggest another way.

Any ideas

Martin