CODEWITHSUNDEEP
excelexcel-formula
Kevin Bolt
Kevin Bolt

Reputation: 1

In spreadsheet, want to find if text values from a range of cells appear within one cell that contains a long string of text

I've spent a few hours on the web trying to find a solution for this with no joy.

Essentially I'm downloading my bank statement in to a spreadsheet. I want to categorise each expenditure by searching its text and seeing if it matches a range of keywords that I've manually added.

For example, Column A contains all my expenses and let's say cell A1 is "TESCO PS5 on 4th May"

I've set up a range of my own text cells in another column such as: C3: MARKS C4: COFFEE C5: TESCO C6: GREENS

Column D contains the following as categories of column C: D3: Food D4: Drink D5: Food D6: Fun

So in this example for A1 I want to add a formula to cell B1 that will search cells C3 to C6 and find the one that is contained within A1. Then it returns the corresponding value from column D.

So in this example, the formula in B1 would search A1, "TESCO PS5 on 4th May" and identify that the value in C5 "TESCO" is contained within A1. It then returns the associated value in D5, "Food"

Thus I'm able to categorise "TESCO PS5 on 4th May" as "Food"

If I use something like Match it seems to do the opposite of what I want. It lets me search for one string within a range of strings. I want the opposite. I want to search if a range of strings are within one string and identify the one that matches.

***************!!!!!!!!EDIT!!!!!!****************

SOLUTION FOUND. Adding it on here as having trouble with formatting using comments.

So I'm using this:

=IFERROR(ArrayFormula(INDIRECT("D"&2+MATCH(TRUE,ISNUMBER(SEARCH($C$3:$C$6,$A1)),0))))

SEARCH: search range for text I want to find in a single cell followed by the single cell I want to search.The result is either an error or an index of a matching search.

ArrayFormula: This allows SEARCH to work the opposite way to almost every example I found says it should be used.Instead of supplying a search term and a search range, instead I supply a range of search terms and specify one single cell to be matched against. ArrayForumla allows for multiple results to be output. It'll print those results one below the other in your spreadsheet. I don't actually want that but I can then use the two following functions to filter out undesired results:

ISNUMBER: Just identifies which result from your search was a valid match. Returns an int.

MATCH: This allows us to filter out all the non-results. Without this the spreadsheet will want to fill out multiple cells with useless data when using ArrayForumla. Using Match we are simply left with the one match result that matters, an index to the matched text field that is contained within our searched cell.

INDIRECT: allows us to build a cell index so we can reference the appropriate category cell data.

IFERROR: just makes sure the spreadsheet doesn't display errors for cells that it doesn't find a solution for.

Upvotes: 0

Views: 1905

Answers (3)

Thomas
Thomas

Reputation: 48

You can try this in B1:
=INDEX($D$3:$D$6,SUMPRODUCT(--ISNUMBER(SEARCH($C$3:$C$6,$A1)),ROW($C$3:$C$6)-2))

Assuming that your list of categories (or keywords you search in A column) in between C3:C6.
And the corresponding value you want to add when each category/keyword found is between D3:D6

It's not elegant, and give you false result on empty cells, but it's a start.

Upvotes: 0

Apurv Pawar
Apurv Pawar

Reputation: 424

As per you ranges I have created this macro. it works at my end. Let me know if you have any problem.

Sub TEST()

Dim i As Integer

i = Range("c65536").End(xlUp).Row
l = Range("a65536").End(xlUp).Row

For k = 1 To l

    For Z = 3 To i

    ck1 = Range("c" & Z).Value
    If InStr(1, Range("a" & k).Value, ck1, vbTextCompare) >= 1 Then
   Range("b" & k).Value = Range("d" & Z).Value
   Exit For
    End If



    Next
Next

End Sub

Upvotes: 0

HaveSpacesuit
HaveSpacesuit

Reputation: 3994

In B1 write =VLOOKUP(A1, C3:D6, 2, TRUE).

You'll need to make sure that C3:D6 is sorted in ascending order by column C.

Info on VLOOKUP

Upvotes: 0

Related Questions