Search string for sub strings

[saltire1963]

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

 

[Peter_SSs]

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?

 

[Trebor76]

Hi saltire1963,

You could create a rule set like this:

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

 

[fhqwgads]

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

Book1
	C	D
1	Category	Narration
2	Entertainment	Netflix
3	Taxi	Uber
4	Electronics	Yodobashi
5	Entertainment	Cineplex
6	Taxi	Yellowcabs
7	Takeout	Pizza Hut
8	Other	Bad Dragon
9	Electronics	JBHiFi
10	Takeout	City Wok
11	Entertainment	Blockbuster Video
12	Other	Paypal
13	Electronics	Radio Shack
14	Taxi	Independent Taxi Co
15	Takeout	Best Thai
Sheet40

Cell Formulas
Range	Formula
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"))))

 

[Peter_SSs]

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"