Help Please - How to search cell contents (text string) for specific set values,but ignore others that contain the same entry - ie look for Rob but ig

U

Upex

Board Regular
Joined
Dec 29, 2010
Messages
186
Office Version
  1. 365
Platform
  1. Windows
Help Please - How to search cell contents (text string) for specific set values but ignore others that contain the same entry – i.e. look for Rob but ignore if found within Robert

Hi All,

Been a while since I've needed any help as my current role doesn't demand much Excel use, however my lack of use have left my knowledge very rusty and as such I have hit a block with the this one and would appreciate your assistance with how to write the code and on how to make this code less bulky and more efficient (it's not slow, for my knowledge and learning).

I have attached an example sheet (if it works), but in summary: (edit: I can't seem to find how to attach - unless it's a video or picture)

I have column J & Y which contain text strings, of any length, structure etc - they are pretty much free type cells.

I want to search column J & Y to see if they contain some specific entries, which can be anywhere within the cell contents. There can be none of the ones I'm looking for, or there can be several within the cells text content.

The stuff I'm looking to find within the cell content is in the format:
1 digit Number, full stop, 1 digit number, full stop then either a 1 or 2 digit number. - e.g. 7.3.1 & 7.3.14

These references aren't terminated in a specific way, i.e. i can have 7.3.1, or 7.3.1R, or 7.3.1. etc.

When one of the specific entries that I'm looking for is found within either the J or Y column, then an 'X' is entered into another column AI:BE that corresponds to the reference it has found (obviously in the same row).

My aim is to search several hundred rows of data and end up with a matrix grid of X's so I can easily see which references have been found and also sort by these X's so I can retrieve the associated row data for further analysis.

All of the above I have achieved (and maybe not by the correct/best methods) by the following code. I should also point out that specific markers are placed in columns A,B & C, but these function fine, so just advising as way of explanation.

My problem is that I'm receiving false results, where I'm looking for a 3 digit reference that features within the 4 digits references. For example, if a cell contains only the 6.5.10 reference, then I get a positive X marker against 6.5.1 as well as 6.5.10, so get a false indication that the cell contains 6.5.1, when it doesn't.

Here’s my question:
How can I make it so that when it's checking for 6.5.1, it excludes 6.5.10? i.e. only counts 6.5.1 when the very next character isn't a number? As I think this would then rule out all the termination that can be after the 3rd number (6.5.1, 6.5.1R, 6.5.1 , etc - this next character can be anything, I just know it wont be a number, or it becomes a difference reference).

Not sure this makes sense, but what I see as the solution is (just can’t do it):

Search for 6.5.1 - if found, then check the next character, if it's a number then we haven't found 6.5.1 so ignore it - if the next character is not a number, then we have found it, so count it and apply the correct marker against 6.5.1.

Any help as to make this specific would be fantastic and if there is a way to write the below code better, please do tell. Just to note though, I need the text in each rows J & Y cells to be checked start to finish, for each reference, not just the first one found etc.

I’ve highlighted the ‘issue references’ below (but not sure if the highlight will pull through to the post), so here are the problem ones, as the others are unique and thus not found within others.

6.5.1
6.5.10
7.6.1
7.6.13
7.6.14
7.6.15
7.4.1
7.4.11
7.4.17

Many thanks all, and here's my fingers crossed!

Upex

Code: 
Sub highlight_Notifiable_CASS_rows_Mark_Up_columnB_and_rule_indicators()
Dim lngI As Long
 
 
For lngI = 1 To Cells.SpecialCells(xlCellTypeLastCell).Row
    If InStr(1, Range("J" & lngI).Value, "6.5.1", vbTextCompare) Then
        Range("B" & lngI).Value = "Yes"
        Range("aj" & lngI).Value = "X"
    End If
        If InStr(1, Range("J" & lngI).Value, "6.5.2", vbTextCompare) Then
            Range("B" & lngI).Value = "Yes"
            Range("ak" & lngI).Value = "X"
        End If
            If InStr(1, Range("J" & lngI).Value, "6.5.6", vbTextCompare) Then
                Range("B" & lngI).Value = "Yes"
                Range("al" & lngI).Value = "X"
            End If
                If InStr(1, Range("J" & lngI).Value, "6.5.10", vbTextCompare) Then
                    Range("B" & lngI).Value = "Yes"
                    Range("am" & lngI).Value = "X"
                End If
                    If InStr(1, Range("J" & lngI).Value, "7.6.1", vbTextCompare) Then
                        Range("B" & lngI).Value = "Yes"
                        Range("an" & lngI).Value = "X"
                    End If
                    If InStr(1, Range("J" & lngI).Value, "7.6.2", vbTextCompare) Then
                        Range("B" & lngI).Value = "Yes"
                        Range("ao" & lngI).Value = "X"
                    End If
                If InStr(1, Range("J" & lngI).Value, "7.6.9", vbTextCompare) Then
                    Range("B" & lngI).Value = "Yes"
                    Range("ap" & lngI).Value = "X"
                End If
            If InStr(1, Range("J" & lngI).Value, "7.6.13", vbTextCompare) Then
                Range("B" & lngI).Value = "Yes"
                Range("aq" & lngI).Value = "X"
            End If
        If InStr(1, Range("J" & lngI).Value, "7.6.14", vbTextCompare) Then
            Range("B" & lngI).Value = "Yes"
            Range("ar" & lngI).Value = "X"
        End If
    If InStr(1, Range("J" & lngI).Value, "7.6.15", vbTextCompare) Then
        Range("B" & lngI).Value = "Yes"
        Range("as" & lngI).Value = "X"
    End If
 
    If InStr(1, Range("Y" & lngI).Value, "6.5.1", vbTextCompare) Then
        Range("B" & lngI).Value = "Yes"
        Range("aj" & lngI).Value = "X"
    End If
        If InStr(1, Range("Y" & lngI).Value, "6.5.2", vbTextCompare) Then
            Range("B" & lngI).Value = "Yes"
            Range("ak" & lngI).Value = "X"
        End If
            If InStr(1, Range("Y" & lngI).Value, "6.5.6", vbTextCompare) Then
                Range("B" & lngI).Value = "Yes"
                Range("al" & lngI).Value = "X"
            End If
                If InStr(1, Range("Y" & lngI).Value, "6.5.10", vbTextCompare) Then
                    Range("B" & lngI).Value = "Yes"
                    Range("am" & lngI).Value = "X"
                End If
                    If InStr(1, Range("Y" & lngI).Value, "7.6.1", vbTextCompare) Then
                        Range("B" & lngI).Value = "Yes"
                        Range("an" & lngI).Value = "X"
                    End If
                    If InStr(1, Range("Y" & lngI).Value, "7.6.2", vbTextCompare) Then
                        Range("B" & lngI).Value = "Yes"
                        Range("ao" & lngI).Value = "X"
                    End If
                If InStr(1, Range("Y" & lngI).Value, "7.6.9", vbTextCompare) Then
                    Range("B" & lngI).Value = "Yes"
                    Range("ap" & lngI).Value = "X"
                End If
            If InStr(1, Range("Y" & lngI).Value, "7.6.13", vbTextCompare) Then
                Range("B" & lngI).Value = "Yes"
                Range("aq" & lngI).Value = "X"
            End If
        If InStr(1, Range("Y" & lngI).Value, "7.6.14", vbTextCompare) Then
            Range("B" & lngI).Value = "Yes"
            Range("ar" & lngI).Value = "X"
        End If
    If InStr(1, Range("Y" & lngI).Value, "7.6.15", vbTextCompare) Then
        Range("B" & lngI).Value = "Yes"
        Range("as" & lngI).Value = "X"
    End If
        
    Next lngI
 
End Sub
 
 
 
 
Sub highlight_Non_Notifiable_CASS_rows_Mark_Up_columnC()
Dim lngI As Long
 
 
For lngI = 1 To Cells.SpecialCells(xlCellTypeLastCell).Row
    If InStr(1, Range("J" & lngI).Value, "7.2.8", vbTextCompare) Then
        Range("C" & lngI).Value = "Yes"
        Range("av" & lngI).Value = "X"
    End If
        If InStr(1, Range("J" & lngI).Value, "7.2.9", vbTextCompare) Then
            Range("C" & lngI).Value = "Yes"
            Range("aw" & lngI).Value = "X"
        End If
            If InStr(1, Range("J" & lngI).Value, "7.2.17", vbTextCompare) Then
                Range("C" & lngI).Value = "Yes"
                Range("ax" & lngI).Value = "X"
            End If
                If InStr(1, Range("J" & lngI).Value, "7.3.1", vbTextCompare) Then
                    Range("C" & lngI).Value = "Yes"
                    Range("ay" & lngI).Value = "X"
                End If
                    If InStr(1, Range("J" & lngI).Value, "7.4.1", vbTextCompare) Then
                        Range("C" & lngI).Value = "Yes"
                        Range("az" & lngI).Value = "X"
                    End If
                        If InStr(1, Range("J" & lngI).Value, "7.4.11", vbTextCompare) Then
                            Range("C" & lngI).Value = "Yes"
                            Range("ba" & lngI).Value = "X"
                        End If
                        If InStr(1, Range("J" & lngI).Value, "7.4.17", vbTextCompare) Then
                            Range("C" & lngI).Value = "Yes"
                            Range("bb" & lngI).Value = "X"
                        End If
                    If InStr(1, Range("J" & lngI).Value, "7.4.22", vbTextCompare) Then
                        Range("C" & lngI).Value = "Yes"
                        Range("bc" & lngI).Value = "X"
                    End If
                If InStr(1, Range("J" & lngI).Value, "7.4.25", vbTextCompare) Then
                    Range("C" & lngI).Value = "Yes"
                    Range("bd" & lngI).Value = "X"
                End If
            If InStr(1, Range("J" & lngI).Value, "8.1.5", vbTextCompare) Then
                Range("C" & lngI).Value = "Yes"
                Range("be" & lngI).Value = "X"
            End If
        If InStr(1, Range("J" & lngI).Value, "8.3.1", vbTextCompare) Then
            Range("C" & lngI).Value = "Yes"
            Range("bf" & lngI).Value = "X"
        End If
    If InStr(1, Range("J" & lngI).Value, "8.3.2", vbTextCompare) Then
        Range("C" & lngI).Value = "Yes"
        Range("bg" & lngI).Value = "X"
    End If
 
    If InStr(1, Range("Y" & lngI).Value, "7.2.8", vbTextCompare) Then
        Range("C" & lngI).Value = "Yes"
        Range("av" & lngI).Value = "X"
    End If
        If InStr(1, Range("Y" & lngI).Value, "7.2.9", vbTextCompare) Then
            Range("C" & lngI).Value = "Yes"
            Range("aw" & lngI).Value = "X"
        End If
            If InStr(1, Range("Y" & lngI).Value, "7.2.17", vbTextCompare) Then
                Range("C" & lngI).Value = "Yes"
                Range("ax" & lngI).Value = "X"
            End If
                If InStr(1, Range("Y" & lngI).Value, "7.3.1", vbTextCompare) Then
                    Range("C" & lngI).Value = "Yes"
                    Range("ay" & lngI).Value = "X"
                End If
                    If InStr(1, Range("Y" & lngI).Value, "7.4.1", vbTextCompare) Then
                        Range("C" & lngI).Value = "Yes"
                        Range("az" & lngI).Value = "X"
                    End If
                        If InStr(1, Range("Y" & lngI).Value, "7.4.11", vbTextCompare) Then
                            Range("C" & lngI).Value = "Yes"
                            Range("ba" & lngI).Value = "X"
                        End If
                        If InStr(1, Range("Y" & lngI).Value, "7.4.17", vbTextCompare) Then
                            Range("C" & lngI).Value = "Yes"
                            Range("bb" & lngI).Value = "X"
                        End If
                    If InStr(1, Range("Y" & lngI).Value, "7.4.22", vbTextCompare) Then
                        Range("C" & lngI).Value = "Yes"
                        Range("bc" & lngI).Value = "X"
                    End If
                If InStr(1, Range("Y" & lngI).Value, "7.4.25", vbTextCompare) Then
                    Range("C" & lngI).Value = "Yes"
                    Range("bd" & lngI).Value = "X"
                End If
            If InStr(1, Range("Y" & lngI).Value, "8.1.5", vbTextCompare) Then
                Range("C" & lngI).Value = "Yes"
                Range("be" & lngI).Value = "X"
            End If
        If InStr(1, Range("Y" & lngI).Value, "8.3.1", vbTextCompare) Then
            Range("C" & lngI).Value = "Yes"
            Range("bf" & lngI).Value = "X"
        End If
    If InStr(1, Range("Y" & lngI).Value, "8.3.2", vbTextCompare) Then
        Range("C" & lngI).Value = "Yes"
        Range("bg" & lngI).Value = "X"
    End If
 
    Next lngI
 
End Sub
 
Last edited:

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
That can be a difficult issue since VBA has its rules for numbers and the InStr function is designed to pick sub-strings out of parent strings. You might have better luck using the Find function in VBA with the LookAt:=xlWhole parameter as part of the arguments. Also, something I came across but have not tried is in the link below. You might want to look at that.

Spreadsheet Page Excel Tips: Is A Particular Word Contained In A Text String?
 
Upvote 0
Thanks JLGWhiz,

I'll check this out, but am dubious at first glance as I'm looking to find number sequences, rather than text/alpha characters.

I'll have a go with the suggested code though and post back.

Thanks for the reply,

Upex
 
Upvote 0
Hi All,

following a hint in another thread, I'm wondering if it's possible to convert the following:

Perhaps

=(MIN(IFERROR(SEARCH("7.3.1"&{1,2,3,4,5,6,7,8,9,0},A1),LEN(A1))) < LEN(A1))
Click to expand...
(thanks to mikerickson for the above)

into VBA code?

i.e.:

Code: 
Dim lngI As Long
For lngI = 1 To Cells.SpecialCells(xlCellTypeLastCell).Row
    If InStr(1, Range("J" & lngI).Value, [B]"6.5.1" & {a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z, ,",",.,}, [/B]vbTextCompare) Then
        Range("B" & lngI).Value = "Yes"
        Range("aj" & lngI).Value = "X"
    End If

but this doesn't work.

Any ideas is it's possible?

Thanks, Upex
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Search function not finding rows containing the matching value
Replies
1
Views
526
GlennUK
G
P
  • Solved
Search for a specific value in a row, then add more data to columns in that same row?
Replies
3
Views
573
dmt32
D
K
  • Locked
  • Question
Search One Column of Worksheet for multiple different partial texts, and if found post a respective value in the same row, one column further
Replies
1
Views
463
Fluff
Fluff
A
  • Question
check for blank cell in a particular column and depending on that make changes in other cell
Replies
0
Views
459
asmi_1758
A
K
  • Question
Search One Column of Worksheet for multiple different partial texts, and if found post a respective value in the same row, one column further
Replies
1
Views
697
karp1
K

Forum statistics

Threads
1,214,827
Messages
6,121,824
Members
449,050
Latest member
Bradel

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top