Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Search for string in Excel column with many rows.

Posted on 2016-10-01
10
Medium Priority
?
82 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
  • 5
  • 4
10 Comments
 
LVL 33

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 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41825193
I have tweaked the formula, please refer to the attached.
test-1--1-.xlsx
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 33

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 33

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 2000 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 33

Expert Comment

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

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

916 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