Identifying vendor in bank statement line detail

[M4ckx]

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.

Purchase authorized on 12/23 Shell Oil 57542184 Porter TX S586358651969453 Card

Purchase authorized on 12/23 Audible Adbl.CO/Bill NJ S466358683933481 Card

Purchase authorized on 12/26 Chachis Mexican Ba Kingwood TX S586362016700893

Purchase authorized on 12/26 Chachis Mexican Ba Kingwood TX S386362053254567

Purchase authorized on 12/27 Buc-Ee's #18 Waller TX P00586362581968682 Card

Purchase authorized on 12/27 Star Stop #63 Porter TX P00306362821478078 Card

<colgroup><col></colgroup><tbody>
</tbody>

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

 

[mole999]

what do you have in the rows

 

[M4ckx]

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.

 

[mole999]

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

 

[M4ckx]

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.

 

[mole999]

this removes the front bit > =MID(A1,30,LEN(A1)-30)

 

[Peter_SSs]

Each line does end with Card XXXX or mearly says Check.

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?

 

[mse330]

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

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
	A	B
1	Purchase authorized on 12/23 Shell Oil 57542184 Porter TX S586358651969453 Card	Shell Oil 57542184 Porter TX
Sheet1

Cell Formulas
Range	Formula
B1	=Clean_Up(A1)

 

[M4ckx]

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.