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

Suppose GREENVILLE, SC 29607 and HIDALGO, TX 78557 are in
Sheets("Sheet1").Range("A1:A2"). The following extracts the state in
each cell.

Sub FindState()
For iR = 1 To 2
str1 = Sheets("Sheet1").Cells(iR, 1)
MsgBox Mid(str1, Len(str1) - 7, 2)
Next iR
End Sub


Hth,
Merjet

Thats not exactly working. I am renaming the sheets to the file name
everytime I run the macro. I guess what I really need is possibly a =mid
formula for cell G2 that finds the state after the comma and space (, ) in
cell W2. I also need it to put "FN" if the comma space isnt found. Any ideas
on that?

"merjet" wrote:

Suppose GREENVILLE, SC 29607 and HIDALGO, TX 78557 are in
Sheets("Sheet1").Range("A1:A2"). The following extracts the state in
each cell.

Sub FindState()
For iR = 1 To 2
str1 = Sheets("Sheet1").Cells(iR, 1)
MsgBox Mid(str1, Len(str1) - 7, 2)
Next iR
End Sub


Hth,
Merjet

Suppose GREENVILLE, SC 29607 and HIDALGO, TX 78557 are in
Sheets("Sheet1").Range("W1:W2"). The following extracts the
state, if any, in each cell.

Sub FindState()
For iR = 1 To 2
str1 = Sheets("Sheet1").Cells(iR, 23)
iPos = InStr(str1, ",")
If iPos 0 then
MsgBox Mid(str1, iPos + 2, 2)
Else
MsgBox "FN"
End If
Next iR
End Sub

Hth,
Merjet

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