Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Jan 2013
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find Specific Text in Cell

    Hi, I have a spreadsheet that contains thousands of rows. I am wanting to, in an adjacent cell, pull out all entries that are like the following:
    (WRN A)
    (WRN B)
    (WRN C)
    (WRN D)

    So, for example if AAAAAAAAAAA (WRN A) appears in cell b2, in cell c2 I'd like to simply have a formula that caused (WRN A) to appear in c2. If AAAAAAAAAA hello appeared in b2 then I would like for c2 to be blank.

    All help is appreciated.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Mitch,

    Here's a UDF (User Defined Function) that will do the trick.

    Code:
    Option Explicit
    
    Function PickWRN(Target As Range) As String
    
    '*** Requires Reference to Microsoft VBScript Regular Expressions 5.5 ***
    
    'Calling format: =PickWRN(Cell to test), e.g. =PickWRN(B1)
    '  To make the formula dragable down a column =PickWrn($B1)
    
       Dim zRegEx As String
       Dim regEx  As New RegExp
       
       zRegEx = "\(WRN [A-D]\)"  'RegEx Pattern to match
       
       With regEx                'Setup RegEx Pattern
           .Global = True
           .MultiLine = False
           .IgnoreCase = False
           .Pattern = zRegEx
       End With
    
       If regEx.Test(Target) Then
                PickWRN = Right(Trim(Target), 7)
            Else
               PickWRN = ""
            End If
    
    End Function  'PickWRN
    Note: you will have to save your Excel file as a .xlsm file for this to work
    ExcelRegEx.PNG

    Test File: Excel - VBA - Using RegEx in UDF.xlsm

    Here's a reference on constructing Regular Expressions in Excel:

    HTH
    Last edited by RetiredGeek; 2016-03-31 at 11:17.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Here's a formula for B2 and then fill down:

    =IF(ISERROR(FIND("(WRN",B2)),"",MID(B2,FIND("(WRN" ,B2),1+FIND(")",B2)-FIND("(WRN",B2)))

    This assumes that the string you want is always in parens and begins with "WRN" and there are no other paren sections in the text.

    This one assumes there is only a single character after "(WRN " so that there are always 7 characters including the parens.

    =IF(ISERROR(SEARCH("(WRN*)",B2)),"",MID(B2,SEARCH( "(WRN*)",B2),7))

    Clip0002.jpg

    UPDATE:

    I changed the "find" version to this:

    =IF(ISERROR(FIND("(WRN",B2)),"",MID(B2,FIND("(WRN" ,B2),1+FIND(")",B2,FIND("(WRN",B2))-FIND("(WRN",B2)))

    [this has been a fun exercise]
    Last edited by kweaver; 2016-03-31 at 12:24.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 648 Times in 591 Posts
    Consider this formula in cell B2 then copy down:

    =IF(MID(RIGHT(A2,7),2,3)="WRN",RIGHT(A2,7),"")

    WRN.png

    HTH,
    Maud

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Maud, it wasn't clear to me that the "WRN" was the last set of 7 characters in parens.
    I thought there might be a case where the text was "AAAA (WRN 1) WWWWW" or the (WRN #) was anywhere in the string.

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 648 Times in 591 Posts
    KW,

    You are correct that my formula will work if the naming convention places the (WRN?) at the end of the string. I was just following the OP's example. Your formula is obviously versatile and would be the better method to choose if the format is inconsistent.

    Interesting that you changed from Search to Find since Search is case-insensitive and allows for wildcard characters as you displayed? What was your reasoning?

    Maud

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I'm afraid there was no specific reasoning, just working on a variety of options or approaches.

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    ..and what happens if the cell contains more than one of the
    (WRN A)
    (WRN B)
    (WRN C)
    (WRN D)
    ..e.g. "AAAA(WRN C)AAAAAA(WRN B)"

    ..should it extract the first, the last, or all of them???

    zeddy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •