Search for string in Excel column with many rows.

I have an Excel spreadsheet with over 23500+ rows that I would like to search column "B" for certain strings.

If one of the strings are found that I'm looking for,  then the word "present' would be put in the next column "C" in the same row.

These are the strings I need to look for.
They can either be lower or higher case letters:
re-start
restart
hold
ice
kill
terminate
inactive
success
force
start


Thanks
test.xlsx
LVL 2
Richard KreidlSoftware DeveloperAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
No you can remove the previous formula from column C.
You will need to place the codes on VBA Module and then only this user defined function will work.
So if you are trying this formula in your original workbook, follow the following steps...

1) Open your workbook and press Alt+F11 to open VB Editor.
2) On VB Editor --> Insert --> Module --> And paste the code given below into the opened code window.
3) Close the VB Editor.
4) Save your file as Macro-Enabled Workbook and you are ready to use the function IsStringFound.

Function IsStringFound(RngToMatch As Range, Criteria As Range) As String
Dim x, str() As String
Dim i As Long, j As Long, cnt As Long
x = Criteria.Value
str() = Split(RngToMatch, " ")
For i = 0 To UBound(str)
   For j = 1 To UBound(x, 1)
      If str(i) <> "" And LCase(GetString(str(i))) = LCase(x(j, 1)) Then
         IsStringFound = "Present"
         Exit Function
      End If
   Next j
Next i
If cnt > 0 Then IsStringFound = "Present"
End Function

Function GetString(str As String) As String
Dim RE As Object, Matches As Object
Set RE = CreateObject("vbscript.regexp")
With RE
   .IgnoreCase = True
   .Pattern = "[A-Z]{1,}"
End With
Set Matches = RE.Execute(str)
If Matches.Count > 0 Then
   GetString = Matches(0)
End If
End Function

Open in new window

0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I have listed the criteria on Sheet2 in the range A1:A10 then used the following Array Formula (which requires confirmation with Ctrl+Shift+Enter instead of Enter alone)

On Sheet1
In C1
=IF(SUM(COUNTIF(B1,"* "&Sheet2!$A$1:$A$10&" *")),"Present","")

Open in new window

Confirm with Ctrl+Shift+Enter and then copy down.

For more details, refer to the attached.
test-1.xlsx
0
 
Richard KreidlSoftware DeveloperAuthor Commented:
HI it looks like it worked but I added another row with the string "ICE" and it didn't find the string in that cell.

I'm attaching the Excel sheet you had sent back with the additional row.
test-1--1-.xlsx
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I have tweaked the formula, please refer to the attached.
test-1--1-.xlsx
0
 
Richard KreidlSoftware DeveloperAuthor Commented:
It seems now to insert present when there's no string present that are listed in sheet 2 tab.

I've added 5 rows where you'll see it hit on, but it shouldn't have.

This is with your new formula.

Thanks
test-1--1-.xlsx
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please find the attached macro-enabled file in which I have placed a user defined function called IsStringFound which requires two argument, cell to match and criteria range to match.
In the column D, you will find the following function in D1 which is copied down.
=IsStringFound(B1,Sheet2!$A$1:$A$10)

Open in new window

test-1--1--1.xlsm
0
 
Richard KreidlSoftware DeveloperAuthor Commented:
Not sure I understand how this is all supposed to work. Am I supposed to use the earlier formula and the function in column D?


I just tried to use the new formula or function by itself and it didn't work.
0
 
Richard KreidlSoftware DeveloperAuthor Commented:
Thanks
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome.
0
 
Rob HensonFinance AnalystCommented:
If you want to find those specific words, then it is worth adding a space before and after each word. Otherwise, it will find all words that contain the string eg looking for "force" will also find "forceful" and "enforce", "kill" will also find "skill" and "killing".

"Re-start" and "Restart" are probably surplus, you already have "start" which occurs in both.

Thanks
Rob H

EDIT: Just spotted Neeraj made this point in your follow on question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.