Solved

Search for string in Excel column with many rows.

Posted on 2016-10-01
10
55 Views
Last Modified: 2016-10-03
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
0
Comment
Question by:rkckjk
  • 5
  • 4
10 Comments
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
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
 
LVL 2

Author Comment

by:rkckjk
Comment Utility
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
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
I have tweaked the formula, please refer to the attached.
test-1--1-.xlsx
0
 
LVL 2

Author Comment

by:rkckjk
Comment Utility
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
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 2

Author Comment

by:rkckjk
Comment Utility
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
 
LVL 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
Comment Utility
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
 
LVL 2

Author Closing Comment

by:rkckjk
Comment Utility
Thanks
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
You're welcome.
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now