CODEWITHSUNDEEP
vbaexcelexcel-formula
mad2
mad2

Reputation: 57

Filter for a word from a string in excel

I am having 10 columns (don't bother columns) and more than 10,000 rows. The data in the rows are like:

Mens Black Pindot Skinny Fit Tuxedo Pants

Mens Pink Ribbed Muscle Fit Sweater

Mens Gold Look Bug Lapel Pin

Mens Black Pin Back Bow Tie

The problem is: I want to filter just the "Pin" word. That means the filtered result should give me just last 2 rows as mentioned and not the rows that contains "Pinkdot" and "Pink"

Any help will be appreciated. THanks

Upvotes: 3

Views: 2216

Answers (4)

Glitch_Doctor
Glitch_Doctor

Reputation: 3034

When using auto filter:

Text Filters > Custom Filter... > 

contains         ▼  |Pin | '(this contains a single space after the word)
  ○ And   ◙ Or
ends with        ▼  |Pin|  '(without the single space)

Which will give you the desired result

Upvotes: 0

CallumDA
CallumDA

Reputation: 12113

Some good VBA solutions given. Here is formula option which will give you a True if exactly Pin is found, or False otherwise (it's not case sensitive).

If you have a string in A1, you can place this in B1 and drag down. (You can place it in K1 or anything really if that's easier)

=OR(OR(LEFT(A1,4)="Pin ",RIGHT(A1,3)="pin"),NOT(ISERROR(SEARCH(" pin ",A1))))

Here's how it works:

  1. Checks if pin (with a space after) can be found at the beginning or exactly pin can be found at the end of the string.
  2. checks if pin (with spaces either side) can be found somewhere in the middle of the string.
  3. Return true in either of these cases, false otherwise.

Clearly at this point you can just filter on the True values.

Upvotes: 2

Shai Rado
Shai Rado

Reputation: 33682

Try the code below (using RegEx object to find exact matches to "Pin").

The RegEx Pattern is .Pattern = "\bPin\b".

Code

Option Explicit

Sub FindPinOnly()

Dim C As Range
Dim RegEx As Object, Match As Object

Set RegEx = CreateObject("vbscript.regexp")
With RegEx
    .MultiLine = False
    .Global = True
    .IgnoreCase = True
    .Pattern = "\bPin\b"  ' Match exactly "Pin" without extra letters
End With

For Each C In Range("A1:J10000") '<-- loop through all cells in Range
    Set Match = RegEx.Execute(C.Value)

    If Match.Count >= 1 Then
        C.Interior.ColorIndex = 46 '<-- in my tests I just colored the cells in Red
    End If
Next C

End Sub

Note: don't be afraid of the loop here, it took less than a second to complete the run of this code.

Edit 1: Allow using the RegEx with multiple words, and color only the specific word's font in red (and not the enitre cell)

Option Explicit

Sub TestRegex()
' This sub is to test the RegEx with multile words (from an array)

Dim StrArr, Elem As Variant

' add words below to array (can add words to array using a Range of cells)
StrArr = Array("Belt", "Shoes", "Sunglass", "Pin")

For Each Elem In StrArr '<-- loop through all elements in array and use RegEx sub to find them in the Range
    Call FindWordOnly(Elem)
Next Elem

End Sub

'========================================================================

Sub FindWordOnly(str As Variant)

Dim C As Range
Dim RegEx As Object, Match As Object

Set RegEx = CreateObject("vbscript.regexp")
With RegEx
    .MultiLine = False
    .Global = True
    .IgnoreCase = True
    .Pattern = "\b" & str & "\b"  ' Match exactly "Pin" without extra letters
End With

For Each C In Range("A1:J10000") '<-- loop through all cells in Range
    Set Match = RegEx.Execute(C.Value)

    If Match.Count >= 1 Then
        ' === only modify the "found" text inside the cell to Red ===
         Dim StartChar As Long

         StartChar = InStr(1, C.Value, str)
         C.Characters(Start:=StartChar, Length:=Len(str)).Font.Color = RGB(255, 0, 0)    
    End If   
Next C

End Sub

Upvotes: 3

Moosli
Moosli

Reputation: 3275

You can work with AutoFilter. With the or criteria you can say that you want all Pin that have a space bevor or a space after the Word. So you can get all Pin Words.

The Code Would look like that:

ActiveSheet.ListObjects("Tabelle1").Range.AutoFilter Field:=1, Criteria1 _
        :="=** Pink**", Operator:=xlOr, Criteria2:="=**Pink **"

Upvotes: 0

Related Questions