Re: FIND function with multi criteria

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Dave Peterson (ec35720_at_msn.com)
Date: 07/23/04 

Date: Thu, 22 Jul 2004 19:54:46 -0500

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 work*** 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!

"goss <" wrote:
>
> Thanks Don
> Appreciate your help
> That definitely gets me what I need in Col J
>
> I'm still trying to figure out a formula using FIND with multiple
> criteria.
>
> Sample strings in Col F
> FODO PURCHASED ON 06/16/04--CHARGE TO FAS# 210000
>
> CONTRACT # 1085 FAS# 263350
>
> CONT# 1098 EVENT DATE: 07/01/2004 FAS# 210132
>
> These samples do not incl all possible variations of data entry:
> Best possbile would be wildcard such as FAS*, but I don't always know
> how many placeholders to parse:
>
> FAS # xxxxxx
> FAS#xxxxxx
> FAS #xxxxxx
> FAS# xxxxxx
> FASxxxxxx
> FAS xxxxxx
>
> There are probably some derivatives I'm not even thinking of
> Is it possible to write a formula to parse all of these possibilities?
>
> Or do I need to turn to VBA?
> My current setup places start position of FAS in Col H
> Col I then uses start to parse FAS # xxxxxx (variants). Your formula
> then parses just the 6 digit.
>
> Thanks much!
>
> ---
> Message posted from http://www.ExcelForum.com/ 

-- 
Dave Peterson
ec35720@msn.com
Quantcast