identify substring between two identifiers

[patl]

i have the following formula

=allocation(input!$A$2:$AK$10000,B7,B9,B11,E6,1)

and i need to find the value between "AK$" and ",". In this case it is 10,000, but it could be any number. Could someone supply me with VBA or spreadsheet formula to do this. Thanks.

 

[VoG]

Try

Sub test()
Dim fmla As String, i As Byte, j As Byte
fmla = Range("A1").Formula
i = InStr(fmla, "AK$")
fmla = Right(fmla, Len(fmla) - i - 2)
j = InStr(fmla, ",")
MsgBox Left(fmla, j - 1)
End Sub

 

[jindon]

Another one

Sub test()
    Dim myFormula As String
    myFormula = Range("a1").Formula
    MsgBox Val(Mid$(myFormula, InStr(myFormula, "AK$")+3))
End Sub