Identifying vendor in bank statement line detail

M4ckx

New Member
Joined
Jul 24, 2012
Messages
4
I have converted bank statements from PDF to Excel and now need help identifying the vendors in the line detail of the transactions. I can clean up some statements using Text to column but need help separating the vendors from the other text. Any help would be greatly appreciated. Once I have identified the vendors the rest of the process of coding the transactions falls into place.

[TABLE="width: 590"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Purchase authorized on 12/23 Shell Oil 57542184 Porter TX S586358651969453 Card [/TD]
[/TR]
[TR]
[TD]Purchase authorized on 12/23 Audible Adbl.CO/Bill NJ S466358683933481 Card [/TD]
[/TR]
[TR]
[TD]Purchase authorized on 12/26 Chachis Mexican Ba Kingwood TX S586362016700893 [/TD]
[/TR]
[TR]
[TD]Purchase authorized on 12/26 Chachis Mexican Ba Kingwood TX S386362053254567 [/TD]
[/TR]
[TR]
[TD]Purchase authorized on 12/27 Buc-Ee's #18 Waller TX P00586362581968682 Card [/TD]
[/TR]
[TR]
[TD]Purchase authorized on 12/27 Star Stop #63 Porter TX P00306362821478078 Card [/TD]
[/TR]
</tbody>[/TABLE]

Above is a copy of some lines from one bank statement. Each line does end with Card XXXX or mearly says Check. Check number, amount, date, etc are in separate columns.

Thank you for your help.

M4ckx
 
Last edited:

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
mole999

Thank you for helping.

Columns 1 Column 2 Column 3 Column 4
Row Date Text Description Credit $ Debt $

Text Description is the detail given in the first post.
 
Upvote 0
not sure of the coding but this is fixed length
Purchase authorized on 12/26 so could be used
so could be removed in a simple formula

then maybe a way of identifying S or P with number and removing the right side
 
Upvote 0
not sure of the coding but this is fixed length
Purchase authorized on 12/26 so could be used
so could be removed in a simple formula

then maybe a way of identifying S or P with number and removing the right side


Pardon, I do not understand your reply. The formatting of my earlier answer was disrupted in the posting. Rows are a single transaction showing date, text string description, credit or debit amount. I am looking for a way to parse the text string and identify the payee.

Thank you.
 
Upvote 0
this removes the front bit > =MID(A1,30,LEN(A1)-30)
 
Upvote 0
Each line does end with Card XXXX or mearly says Check.
:confused:
1. That does not make sense to me, I must be misinterpreting what you are saying. No lines end with "Card XXX" and no lines end with "Check" or even have the text "Check" in them.
2. What are the expected results from that sample data and how do you arrive at those results manually?
 
Upvote 0
This UDF (User Defind Function) should extract the text you're looking for taking into consideration 2 things:

1. Each transaction line starts with "Purchase authorized on xx/xx "
2. Each transaction has the state abbreviation in the description

Code:
Function Clean_Up(txt As String) As String
Dim Arr As Variant, State As String
txt = Right(txt, Len(txt) - 29)
Arr = Array(" AL ", " AK ", " AZ ", " AR ", " CA ", " CO ", " CT ", " DE ", " FL ", _
            " GA ", " HI ", " ID ", " IL ", " IN ", " IA ", " KS ", " KY ", " LA ", _
            " ME ", " MD ", " MA ", " MI ", " MN ", " MS ", " MO ", " MT ", " NE ", _
            " NV ", " NH ", " NJ ", " NM ", " NY ", " NC ", " ND ", " OH ", " OK ", _
            " OR ", " PA ", " RI ", " SC ", " SD ", " TN ", " TX ", " UT ", " VT ", _
            " VA ", " WA ", " WV ", " WI ", " WY ")
    
For x = LBound(Arr) To UBound(Arr)
    If txt Like "*" & Arr(x) & "*" Then State = Arr(x)
Next
Clean_Up = Left(txt, InStr(1, txt, State, vbTextCompare) + 2)
End Function



Book1
AB
1Purchase authorized on 12/23 Shell Oil 57542184 Porter TX S586358651969453 CardShell Oil 57542184 Porter TX
Sheet1
Cell Formulas
RangeFormula
B1=Clean_Up(A1)
 
Last edited:
Upvote 0
Thank you, this has given me ideas. Gets me closer but I was wanting to get only "Shell Oil". This is an ongoing problem. as every bank and sometimes each clas of account (Business, personal, etc) has its own format.
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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