Solved

Searching for Different Text in Multiple Cells...

Posted on 2010-09-24
12
288 Views
Last Modified: 2012-05-10
I am trying to come up with a formula that I can paste into 1 column (Column B) to results from Column A in the following fashion, and I can't seem to get it right no matter what I try. I tried using a nested IF statement if a FIND command and that fails to search past the first test, etc.,

Column A
Test1.front.left.99.jpg
Test1.inside.jpg
Test1.outside.jpg
Test1.rear.jpg
Test1.disc.jpg

Column B
left
inside
outside
rear
disc

I can do this with 1 formula per row, but I have to change the formula to match each criteria, and of course prefer to do it with 1 formula on every row that will search for the text contained within the strings in Column A and return Column B - NOTE that I want to be able to change what I get as a result later if that makes sense.
0
Comment
Question by:fbmartinez
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 24

Expert Comment

by:StephenJR
Comment Utility
You seem to be looking for the text before the jpg, except in the first case. What's the logic?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
Assuming you want the last item before the jpg, but excluding items that are all digits, try adding the UDF below to your VBA project, and then using it in a formula like this:

=RegExpFind(A2,"\w+(?=(\.\d+)?\.jpg)",1)

That uses a "lookahead", as is described in my article http://www.experts-exchange.com/Programming/Languages/Visual_Basic/A_1336-Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html

Patrick
Function RegExpFind(LookIn As String, PatternStr As String, Optional Pos, _
    Optional MatchCase As Boolean = True, Optional ReturnType As Long = 0, _
    Optional MultiLine As Boolean = False)
    
    ' Function written by Patrick G. Matthews.  You may use and distribute this code freely,
    ' as long as you properly credit and attribute authorship and the URL of where you
    ' found the code
    
    ' For more info, please see:
    ' http://www.experts-exchange.com/articles/Programming/Languages/Visual_Basic/Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html
    
    ' This function relies on the VBScript version of Regular Expressions, and thus some of
    ' the functionality available in Perl and/or .Net may not be available.  The full extent
    ' of what functionality will be available on any given computer is based on which version
    ' of the VBScript runtime is installed on that computer
    
    ' This function uses Regular Expressions to parse a string (LookIn), and return matches to a
    ' pattern (PatternStr).  Use Pos to indicate which match you want:
    ' Pos omitted               : function returns a zero-based array of all matches
    ' Pos = 1                   : the first match
    ' Pos = 2                   : the second match
    ' Pos = <positive integer>  : the Nth match
    ' Pos = 0                   : the last match
    ' Pos = -1                  : the last match
    ' Pos = -2                  : the 2nd to last match
    ' Pos = <negative integer>  : the Nth to last match
    ' If Pos is non-numeric, or if the absolute value of Pos is greater than the number of
    ' matches, the function returns an empty string.  If no match is found, the function returns
    ' an empty string.  (Earlier versions of this code used zero for the last match; this is
    ' retained for backward compatibility)
    
    ' If MatchCase is omitted or True (default for RegExp) then the Pattern must match case (and
    ' thus you may have to use [a-zA-Z] instead of just [a-z] or [A-Z]).
    
    ' ReturnType indicates what information you want to return:
    ' ReturnType = 0            : the matched values
    ' ReturnType = 1            : the starting character positions for the matched values
    ' ReturnType = 2            : the lengths of the matched values
    
    ' If you use this function in Excel, you can use range references for any of the arguments.
    ' If you use this in Excel and return the full array, make sure to set up the formula as an
    ' array formula.  If you need the array formula to go down a column, use TRANSPOSE()
    
    ' Note: RegExp counts the character positions for the Match.FirstIndex property as starting
    ' at zero.  Since VB6 and VBA has strings starting at position 1, I have added one to make
    ' the character positions conform to VBA/VB6 expectations
    
    ' Normally as an object variable I would set the RegX variable to Nothing; however, in cases
    ' where a large number of calls to this function are made, making RegX a static variable that
    ' preserves its state in between calls significantly improves performance
    
    Static RegX As Object
    Dim TheMatches As Object
    Dim Answer()
    Dim Counter As Long
    
    ' Evaluate Pos.  If it is there, it must be numeric and converted to Long
    
    If Not IsMissing(Pos) Then
        If Not IsNumeric(Pos) Then
            RegExpFind = ""
            Exit Function
        Else
            Pos = CLng(Pos)
        End If
    End If
    
    ' Evaluate ReturnType
    
    If ReturnType < 0 Or ReturnType > 2 Then
        RegExpFind = ""
        Exit Function
    End If
    
    ' Create instance of RegExp object if needed, and set properties
    
    If RegX Is Nothing Then Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = True
        .IgnoreCase = Not MatchCase
        .MultiLine = MultiLine
    End With
        
    ' Test to see if there are any matches
    
    If RegX.Test(LookIn) Then
        
        ' Run RegExp to get the matches, which are returned as a zero-based collection
        
        Set TheMatches = RegX.Execute(LookIn)
        
        ' Test to see if Pos is negative, which indicates the user wants the Nth to last
        ' match.  If it is, then based on the number of matches convert Pos to a positive
        ' number, or zero for the last match
        
        If Not IsMissing(Pos) Then
            If Pos < 0 Then
                If Pos = -1 Then
                    Pos = 0
                Else
                    
                    ' If Abs(Pos) > number of matches, then the Nth to last match does not
                    ' exist.  Return a zero-length string
                    
                    If Abs(Pos) <= TheMatches.Count Then
                        Pos = TheMatches.Count + Pos + 1
                    Else
                        RegExpFind = ""
                        GoTo Cleanup
                    End If
                End If
            End If
        End If
        
        ' If Pos is missing, user wants array of all matches.  Build it and assign it as the
        ' function's return value
        
        If IsMissing(Pos) Then
            ReDim Answer(0 To TheMatches.Count - 1)
            For Counter = 0 To UBound(Answer)
                Select Case ReturnType
                    Case 0: Answer(Counter) = TheMatches(Counter)
                    Case 1: Answer(Counter) = TheMatches(Counter).FirstIndex + 1
                    Case 2: Answer(Counter) = TheMatches(Counter).Length
                End Select
            Next
            RegExpFind = Answer
        
        ' User wanted the Nth match (or last match, if Pos = 0).  Get the Nth value, if possible
        
        Else
            Select Case Pos
                Case 0                          ' Last match
                    Select Case ReturnType
                        Case 0: RegExpFind = TheMatches(TheMatches.Count - 1)
                        Case 1: RegExpFind = TheMatches(TheMatches.Count - 1).FirstIndex + 1
                        Case 2: RegExpFind = TheMatches(TheMatches.Count - 1).Length
                    End Select
                Case 1 To TheMatches.Count      ' Nth match
                    Select Case ReturnType
                        Case 0: RegExpFind = TheMatches(Pos - 1)
                        Case 1: RegExpFind = TheMatches(Pos - 1).FirstIndex + 1
                        Case 2: RegExpFind = TheMatches(Pos - 1).Length
                    End Select
                Case Else                       ' Invalid item number
                    RegExpFind = ""
            End Select
        End If
    
    ' If there are no matches, return empty string
    
    Else
        RegExpFind = ""
    End If
    
Cleanup:
    ' Release object variables
    
    Set TheMatches = Nothing
    
End Function

Open in new window

0
 
LVL 1

Author Comment

by:fbmartinez
Comment Utility
The logic is I want to find whatever text is in some middle section of the filename - could be right before the extension (.jpg in this case) or could be some characters before that. I want to be able to find whatever word I set my expression to search for.

I tried this:

=IF(FIND("passenger",G33),"Passenger",IF(FIND("interior",G33),"Interior",IF(FIND("profile",G33),"Profile",IF(FIND("r3-4",G33),"Rear34","Wheel"))))

For example, and used it in a line where "interior" is what it should have found and it failed because it didn't immediately find "passenger" given a #VALUE error - when I thought the nesting would cause it to exhaust each condition.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
fbmartinez,With respect, what, exactly, does your example above have to do with your example in the original question?In any event, if you just want to test for a list of words...=MID(RegExpFind(A2,"\.(passenger|interior|profile|Rear34|Wheel)(?=\.)",1,FALSE),2,LEN(A2))Patrick
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
You might try an array formula like:
=IF(COUNT(FIND(B$2:B$6,A2))=0,"",INDEX(B$2:B$6,MIN(IF(ISNUMBER(FIND(B$2:B$6,A2)),ROW(B$2:B$6)-ROW(B$2)+1,""))))

Array formulas must be entered in a special fashion:
1) Hold the Control and Shift keys down
2) Press Enter
3) Release all three keys
Excel should respond by adding curly braces surrounding the formula. If you don't see those curly braces, then select the cell, click in the formula bar and follow steps 1-3 once again.

See sample workbook.

Brad
FindKeywordQ26497868.xlsx
0
 
LVL 1

Author Comment

by:fbmartinez
Comment Utility
I guess I'm not being clear enough, sorry, I want to use some sort of test where it returns a value if I find a specific word or phrase within the text in Column A.

For "Test1.front.left.99.jpg" I want to test to see if "left" is in the filename and then in the result column place "left"
For "Test1.inside.jpg" I want to test to see if "inside" is in the filename and then in the result column place "inside"

The filenames are all in 1 column in Excel.

I tried using your function above but all I get is #NAME? in the result column - it is recorded in a Macro Enabled Workbook, etc., so not sure what the issue is.

Thanks for your patience everyone.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
>>I tried using your function above but all I get is #NAME? in the result column - it is recorded in a Macro Enabled >>Workbook, etc., so not sure what the issue is.Please do the following:1) Go to the VB Editor, and paste the code from http:#a33755948 into a new VBA module2) Make sure your macro security is not set to highBut don't bother with that if Brad's formula is working for you.
0
 
LVL 1

Author Comment

by:fbmartinez
Comment Utility
Brad... This is almost there, but I don't want to use column B in my example as part of the criteria, what I want is column b as my result -your formula uses column b in it's calculation.
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
It is worth noting that FIND is case sensitive. If you prefer a case insensitive match, then replace FIND with SEARCH in my suggested formula.
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
Where are the criteria (search words) stored? That's what all the references to column B are about.
0
 
LVL 1

Author Comment

by:fbmartinez
Comment Utility
Okay, I may be able to simplify this further...  

If I have"
TextA.TextB.jpg

How can I return the text between the 2 periods in this string? So I get TextB only in the result cell.

Thanks again all and again sorry for the confusion - it's quirky I know, but I am almost there....
0
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
Comment Utility
The text between the first two periods may be found as:
=MID(A1,FIND(".",A1)+1,FIND(".",A1,FIND(".",A1)+1)-FIND(".",A1)-1)
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now