×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

"Find text" function

"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.

RE: "Find text" function

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

RE: "Find text" function

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

RE: "Find text" function

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.

CODE

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):

CODE

Found = TextSearch("Steel",StringVar)
  {searches a string variable}

HTH
Mike


RE: "Find text" function

(OP)
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.

CODE

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.

RE: "Find text" function

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.

RE: "Find text" function

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

CODE

=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.

RE: "Find text" function

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

RE: "Find text" function

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

RE: "Find text" function

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

RE: "Find text" function

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.

RE: "Find text" function

ddaya,

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

CODE

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:
  • 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
  • Use of constants for the relevant columns makes it easy to adjust these, if the locations change
  • 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
  • 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)



  • Hope this helps
    Mike

    RE: "Find text" function

    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

    Red Flag This Post

    Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

    Red Flag Submitted

    Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
    The Eng-Tips staff will check this out and take appropriate action.

    Reply To This Thread

    Posting in the Eng-Tips forums is a member-only feature.

    Click Here to join Eng-Tips and talk with other members! Already a Member? Login


    Resources

    Low-Volume Rapid Injection Molding With 3D Printed Molds
    Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
    Design for Additive Manufacturing (DfAM)
    Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
    Taking Control of Engineering Documents
    This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

    Close Box

    Join Eng-Tips® Today!

    Join your peers on the Internet's largest technical engineering professional community.
    It's easy to join and it's free.

    Here's Why Members Love Eng-Tips Forums:

    Register now while it's still free!

    Already a member? Close this window and log in.

    Join Us             Close