"Find text" function
"Find text" function
(OP)
Has anyone developed a function for Excel which will search for a specified text string within a text string or cell?
As an example I would like to search for the word "Steel" within various cells which would contain various phrases containing the word "Steel".
I would like to enter the function into a cell as follows
=if(textsearch("Steel",B1),"Yes","No")
where textsearch is the custom function and steel is the search term.
Any help is appreciated. If I make this one myself I will post it if requested.
As an example I would like to search for the word "Steel" within various cells which would contain various phrases containing the word "Steel".
I would like to enter the function into a cell as follows
=if(textsearch("Steel",B1),"Yes","No")
where textsearch is the custom function and steel is the search term.
Any help is appreciated. If I make this one myself I will post it if requested.





RE: "Find text" function
basically it goes like this
instr(startpos,string that is being searched, search string)
there is also InStrRev which like the name says goes in reverse
so in excel I guess it would be
myint=instr(1,activecell.value,"Steel")
which is going to return a number to where "Steel" starts
if myint > 0 then ....I found some Steel
it is case sensitive so you might want a or for lower case
then you could loop thought the cells checking to see
whats there
HTH at least some
RE: "Find text" function
instr(1,activecell.value,myvar)
so with a little pop up form you could set it for different materials or have a listbox to choose say; steel, brass, copper... what every you want
RE: "Find text" function
Here is a function I cobbled together. May not be the most efficient, but should do what you want. The second parameter takes either a string value or a range.
CODE
Dim OneCell As Range
TextSearch = False
If TypeName(Target) = "Range" Then
For Each OneCell In Target
If InStr(1, OneCell.Text, strSearchFor, vbTextCompare) > 0 Then
TextSearch = True
Exit For
End if
Next OneCell
ElseIf TypeName(Target) = "String" Then
If InStr(1, Target, strSearchFor, vbTextCompare) > 0 Then TextSearch = True
End If
End Function
Example Usage (As a worksheet function):
=IF(TextSearch("Steel",B1),"Yes","No") {your example}
=IF(TextSearch("Steel",B1:C10),"Yes","No") {searches a range of cells}
=IF(TextSearch("Steel",RngName),"Yes","No") {RngName is a named range}
Example Usage (VBA code):
CODE
HTH
Mike
RE: "Find text" function
Here is your code rewritten as TextExtract. It is the same code but now returns a different value when true.
CODE
Dim OneCell As Range
TextExtract = ""
If TypeName(Target) = "Range" Then
For Each OneCell In Target
If InStr(1, OneCell.Text, strSearchFor, vbTextCompare) > 0 Then
TextExtract = strSearchFor
Exit For
End If
Next OneCell
ElseIf TypeName(Target) = "String" Then
If InStr(1, Target, strSearchFor, vbTextCompare) > 0 Then TextExtract = strSearchFor
End If
End Function
I will use it with the Vlookup or index function. I may also use it in the future so it will compare a list of strings in a table to the cell contents and return the value required.
RE: "Find text" function
The function above seems so close, yet I just can't seem to figure out the next step.
Appreciate your help.
RE: "Find text" function
CODE
It does not return which cell in the range contains the text. In the case shown only cell A43 contained "3B". This function will search the text sequentially until the condition is true then it will stop searching. Text search will work the same way.
Note that this function is not case sensitive. It will not return how many times the text is present. A modification can be made to have the function count how many cases of the text appear.
RE: "Find text" function
Can you be more specific about what your data will look like, what the function should do and how you want to use the result. Post some sample data (several cells worth). Also, how will you use this in conjunction with VLookup?
Regards,
Mike
RE: "Find text" function
165T VCG Jul 05 - HK
4468 VCG Aug 05 - TOK
4019 VCG Aug 05 - AUS
266Q VCG May 05 - TOK
MTM V MTT diff
MTM adj
PWO Prov
BON Provision
CER provision
I then want to use a static stable that looks for the text in the above containing anything in the 1st column below and should return the 2nd column (so a vlookup, but requires a search within the full filed above).
Text to look for Result Required
VCG Valuation
MTM MTM
Provision Provision
Prov Provision
The above works fine if you only look for 1 of the fields, but not the whole 2nd table
RE: "Find text" function
Just so I'm clear, confirm or correct the statements below:
A column on a worksheet contains a list such as
165T VCG Jul 05 - HK
4468 VCG Aug 05 - TOK
4019 VCG Aug 05 - AUS
266Q VCG May 05 - TOK
MTM V MTT diff
MTM adj
PWO Prov
BON Provision
CER provision
with each item in a separate cell. This is the range to search.
The substrings to search for (in the above range) are obtained from a two-column table elsewhere on the same worksheet or on a different worksheet. The table has the form
VCG Valuation
MTM MTM
Provision Provision
Prov Provision
If the substring from the first column is found within the search range, the value in the second column is returned.
Assumption: If a substring is found for a given entry in the search range (e.g. VCG in 266Q VCG May 05 - TOK), skip searching for any other substrings. In other words, once a match is made, assume no other substrings from the table will occur in the same entry.
One further question: What is done with the value returned from the table?
Mike
RE: "Find text" function
Your assumptions are correct:
- the original column is from a freeform field and therefore I'm trying to categorise each line using the 2nd "mapping" table
- once there is a 'match' in the 1st column of the 2nd table, there needs to be no further analysis (so if it finds 'VCG' it just returns 'Valuation' and moves on to the next line)
- the result (which is being put in another column) is merely trying to classify the entries into broad categories (so it's a table of 15k adjustments) that I can classify as either Valuation, MTM, Provision etc.
Hopefully this calrifies things & thanks again for the assistance.
RE: "Find text" function
Here is a procedure that will do what you want (explanatory notes to follow):
CODE
Const DATA_ENTRY_COL As Integer = 1
Const DATA_CATEGORY_COL As Integer = 2
Const MAP_KEY_COL As Integer = 1
Const MAP_CATEGORY_COL As Integer = 2
Sub CategorizeFieldEntries()
Dim LastUsedRow As Long
Dim SearchRange As Range
Dim MapRange As Range
Dim SrchCell As Range
Dim MapCell As Range
Dim wksEntries As Worksheet
Dim wksMap As Worksheet
Set wksEntries = ThisWorkbook.Worksheets("Data")
Set wksMap = ThisWorkbook.Worksheets("Map")
With wksMap
LastUsedRow = .Cells(65536, MAP_KEY_COL).End(xlUp).Row
Set MapRange = .Range(.Cells(1, MAP_KEY_COL), .Cells(LastUsedRow, MAP_KEY_COL))
End With
With wksEntries
LastUsedRow = .Cells(65536, DATA_ENTRY_COL).End(xlUp).Row
If LastUsedRow < DATA_FIRST_ROW Then Exit Sub
Set SearchRange = .Range(.Cells(DATA_FIRST_ROW, DATA_ENTRY_COL), .Cells(LastUsedRow, DATA_ENTRY_COL))
For Each SrchCell In SearchRange
For Each MapCell In MapRange
If InStr(1, SrchCell.Text, MapCell.Text, vbTextCompare) > 0 Then
.Cells(SrchCell.Row, DATA_CATEGORY_COL).Value = wksMap.Cells(MapCell.Row, MAP_CATEGORY_COL).Text
Exit For
End If
Next MapCell
Next SrchCell
End With
End Sub
Notes:
Hope this helps
Mike
RE: "Find text" function
Thanks for your help with the above. After I figured out what you were trying to do, it was a breeze to incorporate.
Works a treat - really appreciate the help.
Deepak