#1
|
|||
|
|||
Search for multiple texts in cell, return specific text
Ok, this is a great challange! Honor to the one who solves this one
I'm making a budget. I want to sort my spendings into categories. My spendings are downloaded as a spreadsheet from my bank. It looks like this: Details Sum Category VISA Mc Donalds -130,00 138766 Statoil -640,00 76234 TAXI 20141 -357,00 23454 BurgerKing -130,00 The categories in this example would be: 1: lunch 2: gas Now, I want to search for multiple strings in the A-column and return the correct category for each spending. F.x: I will search for texts like "donalds","burger","oil". If no match is found, the return value should be an empty string. The result should look like: Details Sum Category VISA Mc Donalds -130,00 lunch 138766 Statoil -640,00 gas 76234 TAXI 20141 -357,00 23454 BurgerKing -130,00 lunch I have tried =IFERROR(IF(SEARCH("*donalds*";A1);"lunch";"");"") , but I have only been successful with searching for one string. Using macro is an option, but I want to try solving this one without use of macro. I'll be greatful for all the help you may provide! |
#2
|
|||
|
|||
Without VBA? my goodness I find that so much more difficult. In any case here is a formula that you will need to taylor for each array of strings you want to search.
Code:
=SUMPRODUCT(NOT(ISERR(SEARCH({"onald","ing","oil"},A2)))*{1,2,4}) For example to check for all weekdays in a string the formula would have to look like this. Code:
=SUMPRODUCT(NOT(ISERR(SEARCH({"Mon","Tue","Wed","Thu","Fri"},A2)))*{1,2,4,8,16}) If there is a match found a number will be returned. You can enclose this in a iferror formula to get it to read "" if there is no match. If this were me I would go insane having a bunch of formulas like this when I could just have a script that runs when I click a button and checks the strings in a matter of seconds. No waiting for formulas to calculate and I can add as many search terms as I want. Let me know if you want to do the VBA route and I will be happy to write something up for you. Thanks |
#3
|
|||
|
|||
Hi ExcelledSoftware, I haven't followed the entire logic of your response; however, I would like to see the VBA route just for the fun of it (if it's not too much trouble). I've taken a VBA course years ago and want to see if anything would make sense to me. My job requires increased mastery of Excel so I'll be asking lots of questions as the days go on. Thanks
|
#4
|
|||
|
|||
Yeah I would be happy to write something up for you. I am thinking of it going something like this. on 1 worksheet you have all of your values that you want to search through in column A. on another worksheet in the same workbook you have 2 columns of data. One of the string to search and one of the result, for example McDonalds | Food etc. Then when you have all this entered on the 2nd sheet you run the code that I write and that will give you your result in column B on the first worksheet. Sound good? Let me know and I will write it up.
Thanks |
#5
|
|||
|
|||
Ok here is a code to get you started. This looks at the data on the first worksheet in column A. It will put a result into column B. The strings for it to search get entered on the 2nd worksheet. ex: col A = donalds, col B= food.
The code assumes that you have headers for both worksheets and will start on row 2. You can add as many search strings that you want on the 2nd worksheet. Code:
Option Explicit Sub IdentifyCharge() 'Looks through column A and an array then identifies the type of charge. Dim CheckRow As Long, LastRow As Long, CheckString As String Dim ArrayString As String, TypeArray As Variant, x As Integer Dim ResultString As String, ResultArray As Variant, ResultValue As String Dim wb As Workbook, ws1 As Worksheet, ws2 As Worksheet 'Set the references Set wb = ThisWorkbook Set ws1 = wb.Worksheets(1) Set ws2 = wb.Worksheets(2) LastRow = ws1.Range("A50000").End(xlUp).Row 'Fill in the string with arrays to check For CheckRow = 2 To 50000 If ws2.Range("A" & CheckRow).Value = "" Then Exit For ArrayString = ArrayString & ws2.Range("A" & CheckRow).Value & "|" ResultString = ResultString & ws2.Range("B" & CheckRow).Value & "|" Next CheckRow 'Remove trailing delimiters ArrayString = Mid(ArrayString, 1, Len(ArrayString) - 1) ResultString = Mid(ResultString, 1, Len(ResultString) - 1) 'Convert strings to arrays TypeArray = Split(ArrayString, "|") ResultArray = Split(ResultString, "|") 'Run through the list in worksheet 1 and give the results For CheckRow = 2 To LastRow ResultString = "" CheckString = LCase(ws1.Range("A" & CheckRow).Value) For x = 0 To UBound(TypeArray) If InStr(1, CheckString, LCase(TypeArray(x))) Then ResultString = ResultArray(x) Exit For End If Next x ResultValue = Mid(CheckString, InStr(1, CheckString, "-")) ResultString = ResultValue & " " & ResultString ws1.Range("B" & CheckRow).Value = ResultString Next CheckRow End Sub Let me know how it all works out for you. Thanks |
Tags |
categories, search results, string |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Reference another workbook and return values from the same cell across multiple sheets/tabs | lwls | Excel Programming | 5 | 11-08-2014 02:11 PM |
Search the text in 3rd cell by using texts in 2 cells | Shinaj | Excel Programming | 1 | 05-09-2014 09:17 AM |
Search cell text from Excel into ms-word | ShailShin | Word VBA | 1 | 04-21-2014 02:12 AM |
How do I output text to a specific cell (x,y)? | norwood | Word VBA | 2 | 01-31-2014 08:43 AM |
How to set style automatically for specific texts | ragesz | Word | 2 | 07-25-2013 07:08 AM |