FIND function with multi criteria

Hi ng.
Using xl 2003.

Need to scrub some data before submitting
Text is invoice detail dump from Progress database

Data entry person sometimes enters:
FAS# or FAS #

How can I use FIND with these criteria possibly more to handle these
types of data entry inconsistencies?


---
Message posted from http://www.ExcelForum.com/

Sorry...
Follow-up thought.
String to be searched in column f
Position (result of function) to be placed in column h

Criteria:
FAS#, FAS #, FAS_, FAS , (last one is space)


---
Message posted from http://www.ExcelForum.com/

Hi
why not simply search for 'FAS'?

--
Regards
Frank Kabel
Frankfurt, Germany

"goss " schrieb im Newsbeitrag
...
Sorry...
Follow-up thought.
String to be searched in column f
Position (result of function) to be placed in column h

Criteria:
FAS#, FAS #, FAS_, FAS , (last one is space)


---
Message posted from http://www.ExcelForum.com/

xlpart

--
Don Guillett
SalesAid Software

"goss " wrote in message
...
Hi ng.
Using xl 2003.

Need to scrub some data before submitting
Text is invoice detail dump from Progress database

Data entry person sometimes enters:
FAS# or FAS #

How can I use FIND with these criteria possibly more to handle these
types of data entry inconsistencies?


---
Message posted from http://www.ExcelForum.com/

Or FAS* (xlwhole).

To make sure it starts with FAS.

Don Guillett wrote:

xlpart

--
Don Guillett
SalesAid Software

"goss " wrote in message
...
Hi ng.
Using xl 2003.

Need to scrub some data before submitting
Text is invoice detail dump from Progress database

Data entry person sometimes enters:
FAS# or FAS #

How can I use FIND with these criteria possibly more to handle these
types of data entry inconsistencies?


---
Message posted from http://www.ExcelForum.com/


--

Dave Peterson

Thanks all for your help.
When I typed "Function" in my first post I meant the FIND function
I would like to enter a formula in col H

Col I then uses MID Function to Extract the FAS........

Problem at col I:
Data could be many lengths.
FAS # xxxxxx
FAS# xxxxxx
FAS#xxxxxx

I don't need the FAS stuff, just the 6 digit number


---
Message posted from http://www.ExcelForum.com/

OK
=RIGHT(F6,LEN(F6)-FIND("#",F6))

--
Don Guillett
SalesAid Software

"goss " wrote in message
...
Thanks all for your help.
When I typed "Function" in my first post I meant the FIND function
I would like to enter a formula in col H

Col I then uses MID Function to Extract the FAS........

Problem at col I:
Data could be many lengths.
FAS # xxxxxx
FAS# xxxxxx
FAS#xxxxxx

I don't need the FAS stuff, just the 6 digit number


---
Message posted from http://www.ExcelForum.com/

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/

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!

"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

Dave -
Thanks so much!
Way too cool.


---
Message posted from http://www.ExcelForum.com/