If the state is always preceeded by the comma and space, then you
could use the MID function to locate where the state is. Maybe some
help. James

On Jun 7, 11:44?am, Mike wrote:
Any suggestions for looking for a particular string? I want to
start the search in cell G2 for instance as my helper cell. I need to do a
search on the entire row 3 for the two letter State and then extract it to my
helper cell then copy down. The problem is the state is never in the same
place and its in this format: GREENVILLE, SC 29607 or HIDALGO, TX 78557. Also
If the state cannot be found, I would like the result to be "FN" for Foreign
Nation.

My example below is very similar to what I am looking for. It searches for
Fraud: and the extracts the next 2 rows.

Range("G2").Select
ActiveCell.FormulaR1C1 = _

"=INDEX(RC[1]:RC[241],0,MATCH(""fraud:*"",RC[1]:RC[241],0))&""""&INDEX(RC[1 ]:RC[241],0,MATCH(""fraud:*"",RC[1]:RC[241],0)+1)&""""&INDEX(RC[1]:RC[241], 0,MATCH(""fraud:*"",RC[1]:RC[241],0)+2)"
Range("G2").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Range("G2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False