I'd use a UserDefinedFunction to parse this:
Option Explicit
Function FASNumber(myStr) As Variant
Dim FASPos As Long
Dim CharAfterFAS As String
Dim FoundIt As Boolean
Dim AllowableCharsAfterFAS As String
Dim iCtr As Long
'modify this to include all those "allowable characters"
AllowableCharsAfterFAS = " #-_"
'get rid of any extra spaces
myStr = Application.Trim(myStr)
FoundIt = False
Do
FASPos = InStr(1, myStr, "FAS", vbTextCompare)
If FASPos = 0 _
Or FASPos = Len(myStr) - 3 Then
Exit Do
End If
CharAfterFAS = LCase(Mid(myStr, FASPos + 3, 1))
myStr = Mid(myStr, FASPos + 3)
If IsNumeric(CharAfterFAS) _
Or InStr(1, AllowableCharsAfterFAS, CharAfterFAS, vbTextCompare) > 0 _
Then
FoundIt = True
Exit Do
End If
Loop
If FoundIt = False Then
FASNumber = "Not Found"
Else
'find the first digit
FoundIt = False
Do
If IsNumeric(Left(myStr, 1)) Then
FoundIt = True
Exit Do
Else
myStr = Mid(myStr, 2)
End If
If myStr = "" Then
Exit Do
End If
Loop
'look until next non-numeric character
If FoundIt Then
For iCtr = 1 To Len(myStr)
If IsNumeric(Mid(myStr, iCtr, 1)) = False Then
Exit For
End If
Next iCtr
FASNumber = Mid(myStr, 1, iCtr - 1)
Else
FASNumber = "No Number after FAS"
End If
End If
End Function
And you'd use it in your worksheet like:
=FASNumber(A1)
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
=======
Short course:
Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)
right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side
Paste the code in there.
Now go back to excel.
Type =FASNumber(A1) and watch holy heck break loose!
