Results 1 to 5 of 5

Thread: Search string for sub strings
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2014
    Posts
    65
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Search string for sub strings

    I'm not sure how to fix this.

    I've copied my last years worth of credit card statements (2400 rows) into Excel so that I can analyse the data. In each cell in Column D there is text (the narration provided by the Bank) which could be say PAYPAL *NETFLIX.COM 402***** AU and what I would like to do is to search this text string for certain sub strings, say NETFLIX and if a match is found, I would like to categorise it in the cell before in Column C with say the text string "Entertainment". Then move onto all other Rows and categorise each one etc.

    If the next row say has any of the multiple words "taxi" or "cab" or "Uber" then I may want to put in Column C the text "Taxi".

    SubString = ("taxi", "cab", "Uber")

    For i = 3 To finalrow
    MainString = Range("D" & i)
    If InStr(MainString, SubString) <> 0 Then
    Range("C" & i).Value = "Taxi"
    End If
    Next i

    Thanks in advance

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,969
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Search string for sub strings

    So, one thing we would need is some sort of list of substrings to search for and their corresponding category. You have given one example of taxi/cab/uber with category taxi but could we have say a couple more?
    I would just like to get a realistic feel for how many substrings might be in a category and how many categories there might be.

    What should happen if a description happened to contain substrings from more than one category. (eg "Netflix movie Taxi Driver")

    Can you confirm that you want this done via macro?
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #3
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    4,392
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Search string for sub strings

    Hi saltire1963,

    You could create a rule set like this:

    Code:
    Option Compare Text 'Makes all text comparisons case insensitive
    Option Explicit
    Sub Macro1()
    
        Dim lngLastRow As Long
        Dim lngMyRow As Long
        
        lngLastRow = Cells(Rows.Count, "D").End(xlUp).Row
        
        Application.ScreenUpdating = False
        
        For lngMyRow = 3 To lngLastRow
            'Rule set
            If InStr(Range("D" & lngMyRow), "Netflix") > 0 Then
                Range("C" & lngMyRow).Value = "Entertainment"
            ElseIf InStr(Range("D" & lngMyRow), "Taxi") > 0 Or InStr(Range("D" & lngMyRow), "Cab") > 0 Or InStr(Range("D" & lngMyRow), "Uber") > 0 Then
                Range("C" & lngMyRow).Value = "Taxi"
            End If
        Next lngMyRow
        
        Application.ScreenUpdating = True
    
    End Sub
    HTH

    Robert

  4. #4
    Board Regular
    Join Date
    Jul 2018
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search string for sub strings

    i dont know about VBA but you could try a long and unwieldy formula for column C

    CD
    1CategoryNarration
    2EntertainmentNetflix
    3TaxiUber
    4ElectronicsYodobashi
    5EntertainmentCineplex
    6TaxiYellowcabs
    7TakeoutPizza Hut
    8OtherBad Dragon
    9ElectronicsJBHiFi
    10TakeoutCity Wok
    11EntertainmentBlockbuster Video
    12OtherPaypal
    13ElectronicsRadio Shack
    14TaxiIndependent Taxi Co
    15TakeoutBest Thai

    Sheet40



    Worksheet Formulas
    CellFormula
    C2=IF(SUMPRODUCT(--ISNUMBER(SEARCH({"Netflix","Cineplex","Video"},D2)))>0,"Entertainment",IF(SUMPRODUCT(--ISNUMBER(SEARCH({"Uber","Cab","Taxi"},D2)))>0,"Taxi",IF(SUMPRODUCT(--ISNUMBER(SEARCH({"Pizza","City Wok","Best Thai"},D2)))>0,"Takeout",IF(SUMPRODUCT(--ISNUMBER(SEARCH({"Yodobashi","Radio Shack","JBHiFi"},D2)))>0,"Electronics","Other"))))


  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,969
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Search string for sub strings

    Quote Originally Posted by fhqwgads View Post
    i dont know about VBA but you could try a long and unwieldy formula for column C
    I think we need more information about the scale of the problem but a preliminary comment about this formula is that it returns "Taxi" if the OP purchased a "Sword Scabbard" or a bottle of "Cabernet Sauvignon"
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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