"Find text" function 1

[bpeirson]

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
[tt]=if(textsearch("Steel",B1),"Yes","No")[/tt]
where [tt]textsearch[/tt] is the custom function and [tt]steel[/tt] is the search term.

Any help is appreciated. If I make this one myself I will post it if requested.

 

[cadcamguy]

the instr function may be something you want to look into

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 ...not really a excel guy

 

[cadcamguy]

Also the search string could be a variable
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

 

[msmith]

bpeirson,

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.

Function TextSearch(ByVal strSearchFor, ByVal Target As Variant) As Boolean
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):

Found = TextSearch("Steel",StringVar)

{searches a string variable}

HTH
Mike

 

[bpeirson]

Thanks for the code. I had to modify it for efficient use in my application. I realised that once I found a string I would have to insert an appropriate value from a table.

Here is your code rewritten as TextExtract. It is the same code but now returns a different value when true.

Function TextExtract(ByVal strSearchFor, ByVal Target As Variant) As String
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.

 

[ddaya]

I need to use something similar to the textsearch/textextract functions above, but is is possible to have the text search as a range (so searching for multiple strings within text) and returning just the string on which I can use a vlookup function.

The function above seems so close, yet I just can't seem to figure out the next step.

Appreciate your help.

 

[bpeirson]

I have used this function in a spreadsheet and it works with more than a single cell.

=TextExtract("3b",A40:A46)

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.

 

[msmith]

ddaya,

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

 

[ddaya]

An example of what I'm trying to do - I'st set of data is what where I'm searching within the cell :

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

 

[msmith]

ddaya,

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

 

[ddaya]

Mike;

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.

 

[msmith]

ddaya,

Here is a procedure that will do what you want (explanatory notes to follow):

Const DATA_FIRST_ROW As Long = 2
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:
[li]This is set up using 2 worksheets, one for the data and a second for the category map, which allows maximum flexibility including hiding the map sheet[/li]
[li]Use of constants for the relevant columns makes it easy to adjust these, if the locations change[/li]
[li]The number of rows of data and map entries are dynamically determined, so these can be adjusted on the worksheets without requiring any changes to the procedure[/li]
[li]I incorporated the essential logic of the TextSearch function directly into the CategorizeFieldEntries procedure. This eliminates making calls to a secondary procedure inside a loop (with larger data sets, you gain some speed)[/li]


Hope this helps
Mike

 

[ddaya]

Mike;

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