Solved

Search for string in Excel column with many rows.

Posted on 2016-10-01
10
78 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:Richard Kreidl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41825096
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:Richard Kreidl
ID: 41825109
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 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41825193
I have tweaked the formula, please refer to the attached.
test-1--1-.xlsx
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 2

Author Comment

by:Richard Kreidl
ID: 41825250
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 31

Expert Comment

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

Author Comment

by:Richard Kreidl
ID: 41825270
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 31

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41825273
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:Richard Kreidl
ID: 41825316
Thanks
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41825444
You're welcome.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41826419
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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

615 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