VBA Code for searching exact text with or without spacing

[Justijb]

Hello all, Please help.

So I have one column of information that is inconsistent to say the least. It has different spacing, hyphens, wording. Essentially it is a comment box filled with different information.

What I need is a script or mechanism to only search certain numbers and text

1) 2 RCA
2) 30 DIAC

That's it. There's over 17k lines of entry and I need to only isolate column f with 1) + 2) data.

In some rows "2 DCA" has a hyphen (2-DCA) or 2 space - DCA, in others it has a space; same with 30 DIAC

Any assistance would be very helpful.

Thank you.

 

[Justijb]

Hopefully looking to bump this. I'm stumped.

 

[NdNoviceHlp]

This might get U started. HTH. Dave

Sub test()
Dim Lastrow As Double, Rng As Range, r As Range
With Sheets("Sheet1")
Lastrow = .Cells(.Rows.Count, "F").End(xlUp).Row
Set Rng = .Range(.Cells(1, "F"), .Cells(Lastrow, "F"))
End With
For Each r In Rng
If InStr(r.Text, "2 -DCA") Or _
InStr(r.Text, "2 DCA") Or _
InStr(r.Text, "2-DCA") Then
'do stuff here
MsgBox r.Row
End If
Next r
End Sub

 

[NdNoviceHlp]

This is better. Works for 322 DCA , 12 DCA etc. as a filter but not for 22 DCA. Dave

Sub test()
Dim Lastrow As Double, Rng As Range, r As Range
With Sheets("Sheet1")
Lastrow = .Cells(.Rows.Count, "F").End(xlUp).Row
Set Rng = .Range(.Cells(1, "F"), .Cells(Lastrow, "F"))
End With
For Each r In Rng
If r.Text Like "2*DCA" Then
'do stuff here
'MsgBox r.Row & "   " & r.Rows
End If
Next r
End Sub

 

[Joe4]

Hopefully looking to bump this. I'm stumped.

In the future, please wait more than 9 minutes to bump! What you may not realize is that bumping so soon actually reduces your chances of looks/replies, as that will remove the thread from the "Zero Reply Posts" list that many of us use to look for unanswered questions.

Our recommendation is to wait 24 hours before bumping, so it stays on that list for a full day.