Excel 2007: Extract Text String based on a Defined List of Criteria

J

justr8hr

New Member
Joined
May 16, 2013
Messages
21
Hello guys,
Please help this newbie.:confused:
I want to search the vendor/Text String (from bank statement)on the list below.
[TABLE="width: 640"]
<tbody>[TR]
[TD]KEEP THE CHANGE TRANSFER TO ACCT
[/TD]
[/TR]
[TR]
[TD]POS #209700 AMERICAN RANCH 12-28 CUSTOMER 1311 PURCHASE ARTESIA CA
[/TD]
[/TR]
[TR]
[TD]STARBUCKS CORP 12/29 PURCHASE 2 SANTA MONICA, CA
[/TD]
[/TR]
[TR]
[TD]ID:BX0364457 IDS PROPERTY CAS DES: PREM
[/TD]
[/TR]
[TR]
[TD]POS 4457311483 MACY'S EAST 483 12/29 CARD PURCHASE CERRITOS, CA
[/TD]
[/TR]
[TR]
[TD]TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CA
[/TD]
[/TR]
[TR]
[TD]CARLTON HAIR INTERNATIO 12/28 CARD PURCHASE
[/TD]
[/TR]
[TR]
[TD]KEEP THE CHANGE TRANSFER TO ACCT
[/TD]
[/TR]
[TR]
[TD]POS CSMC CAFETERIA4007 12/27 PURCHASE
[/TD]
[/TR]
</tbody>[/TABLE]
Initially I am doing this manually. I have decided to document my input so I don't have to input them one by one, only if I just know to use them as reference to automatically extract the text.
Here is My criteria list (the actual one is more comprehensive)
[TABLE="width: 665"]
<tbody>[TR]
[TD]VENDOR
[/TD]
[TD]KIND/CATEGORY
[/TD]
[/TR]
[TR]
[TD]American Ranch
[/TD]
[TD]Groceries
[/TD]
[/TR]
[TR]
[TD]Anthem Blue
[/TD]
[TD]Health Insurance
[/TD]
[/TR]
[TR]
[TD]Autozone
[/TD]
[TD]Car Repairs
[/TD]
[/TR]
[TR]
[TD]Carlton Hair
[/TD]
[TD]Personal Care
[/TD]
[/TR]
[TR]
[TD]Cheesecake Factory
[/TD]
[TD]Meals
[/TD]
[/TR]
[TR]
[TD]Chevron
[/TD]
[TD]Fuel
[/TD]
[/TR]
[TR]
[TD]CSMC CAFETERIA
[/TD]
[TD]Restaurants/Dining
[/TD]
[/TR]
</tbody>[/TABLE]

Once I am able to extract the text based on the criteria, I can then put the right category.
VBA or Excel solutions will be greatly appreciated
Thanks a lot!
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
justr8hr,

Welcome to the MrExcel forum.

Sample criteria data in worksheet Criteria:


Excel 2007
AB
1VENDORKIND/CATEGORY
2American RanchGroceries
3Anthem BlueHealth Insurance
4AutozoneCar Repairs
5Carlton HairPersonal Care
6Cheesecake FactoryMeals
7ChevronFuel
8CSMC CAFETERIARestaurants/Dining
9
Criteria


Sample raw data in worksheet Sheet1:


Excel 2007
AB
1KEEP THE CHANGE TRANSFER TO ACCT
2POS #209700 AMERICAN RANCH 12-28 CUSTOMER 1311 PURCHASE ARTESIA CA
3STARBUCKS CORP 12/29 PURCHASE 2 SANTA MONICA, CA
4ID:BX0364457 IDS PROPERTY CAS DES: PREM
5POS 4457311483 MACY'S EAST 483 12/29 CARD PURCHASE CERRITOS, CA
6TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CA
7CARLTON HAIR INTERNATIO 12/28 CARD PURCHASE
8KEEP THE CHANGE TRANSFER TO ACCT
9POS CSMC CAFETERIA4007 12/27 PURCHASE
10
Sheet1


After the macro:


Excel 2007
AB
1KEEP THE CHANGE TRANSFER TO ACCT
2POS #209700 AMERICAN RANCH 12-28 CUSTOMER 1311 PURCHASE ARTESIA CAGroceries
3STARBUCKS CORP 12/29 PURCHASE 2 SANTA MONICA, CA
4ID:BX0364457 IDS PROPERTY CAS DES: PREM
5POS 4457311483 MACY'S EAST 483 12/29 CARD PURCHASE CERRITOS, CA
6TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CA
7CARLTON HAIR INTERNATIO 12/28 CARD PURCHASE Personal Care
8KEEP THE CHANGE TRANSFER TO ACCT
9POS CSMC CAFETERIA4007 12/27 PURCHASE Restaurants/Dining
10
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Option Explicit
Sub FindKindCategory()
' hiker95, 05/16/2013
' http://www.mrexcel.com/forum/excel-questions/703193-excel-2007-extract-text-string-based-defined-list-criteria.html
Dim a As Variant, b As Variant, c As Variant
Dim i As Long, ii As Long
c = Sheets("Criteria").Cells(1).CurrentRegion
With Sheets("Sheet1")
  .Columns(2).ClearContents
  a = .Cells(1).CurrentRegion
  ReDim b(1 To UBound(a, 1), 1 To 1)
  For i = 1 To UBound(a, 1)
    For ii = 2 To UBound(c, 1)
      If InStr(a(i, 1), UCase(c(ii, 1))) > 0 Then
        b(i, 1) = c(ii, 2)
        Exit For
      End If
    Next ii
  Next i
  .Cells(1, 2).Resize(UBound(b, 1)) = b
  .Columns(2).AutoFit
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the FindKindCategory macro.
 
Upvote 0
You are amazing hiker95 !
Sorry if I have not made clear the output that I was expecting. The way I looked at your response is that you search the criteria list for the vendor then go over the long strings, then put the appropriate category. This by itself is amazing, worthy of praise!
Is there a way that you can tweak this a bit?
Instead of putting the category right away, can you please extract the text from the long string. If the extracted text is similar or matches the one from the Criteria List, then follow the category on that Criteria List.
Something like this:
[TABLE="width: 811"]
<tbody>[TR]
[TD]COLUMN 1
[/TD]
[TD]COLUMN2
[/TD]
[TD]COLUMN 3
[/TD]
[/TR]
[TR]
[TD]Long Text String where the vendor is to be extracted
[/TD]
[TD]Extracted text
[/TD]
[TD]Category: (if the extracted text found in column 2 is similar or matches with the Criteria List, follow the Category found on the Criteria List
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]If there is no match - - - either an ERROR or an option to add to the Category List
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
justr8hr,

With the same two beginning screenshots in my last reply.

After the updated macro per your latest request:


Excel 2007
ABC
1KEEP THE CHANGE TRANSFER TO ACCTError
2POS #209700 AMERICAN RANCH 12-28 CUSTOMER 1311 PURCHASE ARTESIA CAAMERICAN RANCHGroceries
3STARBUCKS CORP 12/29 PURCHASE 2 SANTA MONICA, CAError
4ID:BX0364457 IDS PROPERTY CAS DES: PREM Error
5POS 4457311483 MACY'S EAST 483 12/29 CARD PURCHASE CERRITOS, CAError
6TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CAError
7CARLTON HAIR INTERNATIO 12/28 CARD PURCHASE CARLTON HAIRPersonal Care
8KEEP THE CHANGE TRANSFER TO ACCT Error
9POS CSMC CAFETERIA4007 12/27 PURCHASE CSMC CAFETERIARestaurants/Dining
10
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code: 
Option Explicit
Sub FindKindCategoryV2()
' hiker95, 05/16/2013
' http://www.mrexcel.com/forum/excel-questions/703193-excel-2007-extract-text-string-based-defined-list-criteria.html
Dim a As Variant, b As Variant, c As Variant
Dim i As Long, ii As Long, e As Long
c = Sheets("Criteria").Cells(1).CurrentRegion
With Sheets("Sheet1")
  .Columns("B:C").ClearContents
  a = .Cells(1).CurrentRegion
  ReDim b(1 To UBound(a, 1), 1 To 2)
  For i = 1 To UBound(a, 1)
    e = 0
    For ii = 2 To UBound(c, 1)
      If InStr(a(i, 1), UCase(c(ii, 1))) = 0 Then
        e = e + 1
      ElseIf InStr(a(i, 1), UCase(c(ii, 1))) > 0 Then
        b(i, 1) = UCase(c(ii, 1))
        b(i, 2) = c(ii, 2)
        Exit For
      End If
      If e > 0 Then
        b(i, 1) = "Error"
      End If
    Next ii
  Next i
  .Cells(1, 2).Resize(UBound(b, 1), UBound(b, 2)) = b
  .Columns("B:C").AutoFit
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the FindKindCategoryV2 macro.
 
Upvote 0
You rock hiker95, kudos!
i tested it with the data I sent you and it worked.
i copied many times the same files and run the macro and it worked
however when I added some items on the criteria sheet then added exactly the same item on the source sheet,
the macro returned an error as if those items are not in the criteria sheet

pls extend me more of ur patience thanks
 
Upvote 0
justr8hr,

Thanks for the feedback.

You are very welcome. Glad I could help.

however when I added some items on the criteria sheet then added exactly the same item on the source sheet,
the macro returned an error as if those items are not in the criteria sheet
pls extend me more of ur patience thanks
Click to expand...

In order to get it right the next time I will need the workbook that the macro failed on.

You can upload your workbook to Box Net,
sensitive data scrubbed/removed/changed
mark the workbook for sharing
and provide us with a link to your workbook.
 
Upvote 0
Hello hiker95 Per your advice I opened a Box account and tried to make the link (I just hope it works, I am no savvy!). The file is a macro-enabled workbook. One of the sheets is named "Criteria" where I put now the more populated list, which will eventually grow. So please indulge me with an option to populate it as may be required. Here's the link and thank you so much for your time and intellect: https://www.box.com/s/umebswve02vfjtqtu2o5
 
Upvote 0
justr8hr,

Thanks for the workbook.

Sample raw data in worksheet Sheet1:


Excel 2007
ABC
1KEEP THE CHANGE TRANSFER TO ACCT
2POS #209700 AMERICAN RANCH 12-28 CUSTOMER 1311 PURCHASE ARTESIA CA
3STARBUCKS CORP 12/29 PURCHASE 2 SANTA MONICA, CA
4ID:BX0364457 IDS PROPERTY CAS DES: PREM
5POS 4457311483 MACY'S EAST 483 12/29 CARD PURCHASE CERRITOS, CA
6TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CA
7CARLTON HAIR INTERNATIO 12/28 CARD PURCHASE
8KEEP THE CHANGE TRANSFER TO ACCT
9POS CSMC CAFETERIA4007 12/27 PURCHASE
10POS 4457311483 MACY'S EAST 483 12/29 CARD PURCHASE CERRITOS, CA
11TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CA
12TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CA
13STARBUCKS CORP 12/29 PURCHASE 2 SANTA MONICA, CA
14TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CA
15ID:BX0364457 IDS PROPERTY CAS DES: PREM
16KEEP THE CHANGE TRANSFER TO ACCT
17POS #209700 AMERICAN RANCH 12-28 CUSTOMER 1311 PURCHASE ARTESIA CA
18STARBUCKS CORP 12/29 PURCHASE 2 SANTA MONICA, CA
19ID:BX0364457 IDS PROPERTY CAS DES: PREM
20POS 4457311483 MACY'S EAST 483 12/29 CARD PURCHASE CERRITOS, CA
21TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CA
22CARLTON HAIR INTERNATIO 12/28 CARD PURCHASE
23KEEP THE CHANGE TRANSFER TO ACCT
24POS CSMC CAFETERIA4007 12/27 PURCHASE
25POS 4457311483 MACY'S EAST 483 12/29 CARD PURCHASE CERRITOS, CA
26TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CA
27TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CA
28STARBUCKS CORP 12/29 PURCHASE 2 SANTA MONICA, CA
29TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CA
30ID:BX0364457 IDS PROPERTY CAS DES: PREM
31Albertsons
32Chevron
33
Sheet1


After the new macro:


Excel 2007
ABC
1KEEP THE CHANGE TRANSFER TO ACCTKEEP THE CHANGE TRANSFER TO ACCT
2POS #209700 AMERICAN RANCH 12-28 CUSTOMER 1311 PURCHASE ARTESIA CAError
3STARBUCKS CORP 12/29 PURCHASE 2 SANTA MONICA, CASTARBUCKSMeals & Entertainment
4ID:BX0364457 IDS PROPERTY CAS DES: PREM Error
5POS 4457311483 MACY'S EAST 483 12/29 CARD PURCHASE CERRITOS, CAMACY'SCLOTHES/SHOES
6TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CATARGETGROCERIES
7CARLTON HAIR INTERNATIO 12/28 CARD PURCHASEError
8KEEP THE CHANGE TRANSFER TO ACCTKEEP THE CHANGE TRANSFER TO ACCT
9POS CSMC CAFETERIA4007 12/27 PURCHASE Error
10POS 4457311483 MACY'S EAST 483 12/29 CARD PURCHASE CERRITOS, CAMACY'SCLOTHES/SHOES
11TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CATARGETGROCERIES
12TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CATARGETGROCERIES
13STARBUCKS CORP 12/29 PURCHASE 2 SANTA MONICA, CASTARBUCKSMeals & Entertainment
14TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CATARGETGROCERIES
15ID:BX0364457 IDS PROPERTY CAS DES: PREM Error
16KEEP THE CHANGE TRANSFER TO ACCTKEEP THE CHANGE TRANSFER TO ACCT
17POS #209700 AMERICAN RANCH 12-28 CUSTOMER 1311 PURCHASE ARTESIA CAError
18STARBUCKS CORP 12/29 PURCHASE 2 SANTA MONICA, CASTARBUCKSMeals & Entertainment
19ID:BX0364457 IDS PROPERTY CAS DES: PREM Error
20POS 4457311483 MACY'S EAST 483 12/29 CARD PURCHASE CERRITOS, CAMACY'SCLOTHES/SHOES
21TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CATARGETGROCERIES
22CARLTON HAIR INTERNATIO 12/28 CARD PURCHASEError
23KEEP THE CHANGE TRANSFER TO ACCTKEEP THE CHANGE TRANSFER TO ACCT
24POS CSMC CAFETERIA4007 12/27 PURCHASE Error
25POS 4457311483 MACY'S EAST 483 12/29 CARD PURCHASE CERRITOS, CAMACY'SCLOTHES/SHOES
26TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CATARGETGROCERIES
27TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CATARGETGROCERIES
28STARBUCKS CORP 12/29 PURCHASE 2 SANTA MONICA, CASTARBUCKSMeals & Entertainment
29TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CATARGETGROCERIES
30ID:BX0364457 IDS PROPERTY CAS DES: PREM Error
31AlbertsonsALBERTSONSGROCERIES
32ChevronCHEVRONFUEL
33
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code: 
Option Explicit
Sub FindKindCategoryV3()
' hiker95, 05/18/2013
' http://www.mrexcel.com/forum/excel-questions/703193-excel-2007-extract-text-string-based-defined-list-criteria.html
Dim a As Variant, b As Variant, c As Variant
Dim i As Long, ii As Long, e As Long, lr As Long
With Sheets("Criteria")
  lr = .Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
  c = .Range(.Cells(2, 1), .Cells(lr, 2))
End With
With Sheets("Sheet1")
  .Columns("B:C").ClearContents
  a = .Cells(1).CurrentRegion
  ReDim b(1 To UBound(a, 1), 1 To 2)
  For i = 1 To UBound(a, 1)
    e = 0
    For ii = 2 To UBound(c, 1)
      If InStr(UCase(a(i, 1)), UCase(c(ii, 1))) = 0 Then
        e = e + 1
      ElseIf InStr(UCase(a(i, 1)), UCase(c(ii, 1))) > 0 Then
        b(i, 1) = UCase(c(ii, 1))
        b(i, 2) = c(ii, 2)
        Exit For
      End If
      If e > 0 Then
        b(i, 1) = "Error"
      End If
    Next ii
  Next i
  .Cells(1, 2).Resize(UBound(b, 1), UBound(b, 2)) = b
  For i = 1 To UBound(b, 1)
    If .Cells(i, 2) = "Error" Then
      .Cells(i, 2).Interior.Color = 255
    End If
  Next i
  .Columns("B:C").AutoFit
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the FindKindCategoryV3 macro.


I have examined the results after the macro in worksheet Sheet1, and, can not determine the logic for the Error's in column B, to be able to pick the correct part of the respective string in column A, to be moved to worksheet Criteria. Each string has a different structure?
 
Upvote 0
Thanks a lot hiker95

yes each source string has different structure because they were extracts from bank statements.
Kinda weird because those that appeared as errors were identified on your macro version 2!

 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,247
Messages
6,171,007
Members
452,374
Latest member
keccles

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top