Find characters in formula

[mtbe]

In the attached file, I am looking for the 2 numbers after the FW in the formula in cells in column G.

Example:
Cell G258 has the formula:

='Z:\PRODMONTH\Production2009\[Fw37_09.xls]Sun'!$C$16+'Z:\PRODMONTH\Production2009\[Fw37_09.xls]Sun'!$D$16

I am trying to set a parameter (J in my instance) to equal the 37 in my VBA code, first two digits after the 'Fw'. (Essentially, this is the Fiscal Week - but this can change from site to site - some start in October, others in Jan, sometimes late December, etc), so I need these numbers and not create another application to create the Fiscal Week.

[DonkeyOte]

Perhaps

Dim j As Long
j = Val(Split(Cells(258,"G").Formula,"Fw")(1))

[Leith Ross]

Hello mtbe,

Use this UDF (User Defiend Function) in a cell on your worksheet or in your VBA code to return the Fiscal Week number from a cell. If "FW" is found the week number is returned, otherwise a zero is returned.

Function GetFiscalWeek(Cell As Range)

  Dim I As Integer
  
    I = InStr(1, Cell.Formula, "FW", vbTextCompare)
    GetFiscalWeek = IIf(I > 0, Mid(Cell.Formula, I + 2, 2), 0)
    
End Function

Example - Worksheet Function

  'Cell $E$258 formula
   =GetFiscalWeek(G258)

  'Cell $E$258 value
   37