Parse BOA Bank Transactions

[buggabooed]

Hi all. I am working on a new budget for the household, and the one thing I've found repeatedly is I hate entering in all the transactions when I can download the CVS transaction file from BOA, and theoretically parse out what I am wanting from there. But I am stuck!

Each transaction is put into a row like such with 4 columns (Date, Transaction Type, Amount, Balance):
1/2/2015 |CHECKCARD 5127 SONIC DRIVE IN #8524 Place State 254632187455132165749812 | -9.53 | 4759.34

I would like to extract the key word for each transaction in the second column (B):
Paycheck
Counter Credit
Checkcard
Purchase
Payment
Deposit

and place that into a new column where I can act on it accordingly. The catch is the key word is always in a different place, and of course differs on each line based on what the transaction was.

Is there a formula that I can utilize that will check the (B) column for each of those words, and when it finds the one in it, say "Purchase", spit that out into column (E)?

Any help would certainly be appreciated. Thank you.

 

[sheetspread]

Excel 2010
	A	B
1	CHECKCARD	CHECKCARD 5127 SONIC DRIVE IN #8524 Place State 254632187455132165749812
Sheet6

Cell Formulas
Range	Formula
A1	=LEFT(B1,FIND(" ",B1,"1"))

counter credit will read as counter, because you're looking for the first word, but is that ok? (simplest method)

Or are there no delimiters and you actually need a formula containing the search terms in an array to variably select position-wise?

 

[buggabooed]

Thank you for the reply. For counter that would work just fine.

Checkcard is the first word on the above transaction, but paycheck, purchase, deposit... don't occur at the beginning, but at random places within the B cell. And each row will be different, so it might be 3 checkcards on 3 rows, or a checkcard, a purchase, a deposit in the same 3 rows. Does that make sense?

 

[sheetspread]

Ok, hang on.......

 

[buggabooed]

Will do. Thank you.

 

[sheetspread]

Excel 2010
	A	B
1	CHECKCARD	CHECKCARD 5127 SONIC DRIVE IN #8524 Place State 254632187455132165749812
2	COUNTER CREDIT	fahfjkas,flkjasjkfafaskdjlfCOUNTER CREDITjksaljlkfsdjlkfajlkfsljaklfsals
3	DEPOSIT	sdkjf ksjakfjk fjkfajks djsdkx DEPOSIT cncmxcmxccnxcm
4	PAYMENT	ewwe iuwei utiuteiueriu PAYMENT reireireiureireireireiuaaa
Sheet5 (4)

Cell Formulas
Range	Formula
A1	{=INDEX({"PAYCHECK","COUNTER CREDIT","CHECKCARD","PURCHASE","PAYMENT","DEPOSIT"},SUM(IF(ISERROR(FIND({"PAYCHECK","COUNTER CREDIT","CHECKCARD","PURCHASE","PAYMENT","DEPOSIT"},B1)),0,MATCH({"PAYCHECK","COUNTER CREDIT","CHECKCARD","PURCHASE","PAYMENT","DEPOSIT"},{"PAYCHECK","COUNTER CREDIT","CHECKCARD","PURCHASE","PAYMENT","DEPOSIT"},0))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

This is awful, but fast and I think it works (strings not containing any of the search terms will return the first one rather than a blank). Someone will likely show up with a better one, but I'll post another otherwise.

 

[buggabooed]

Dude! Thank you very much. It definitely works for what I am needing, and I can make modifications if needed. I really appreciate your help. Thank you!

 

[buggabooed]

Quick question...If I end up wanting to edit this, and say add another value, would I just add the value to each spot like you did above? Or say I would rather have a blank show then the first one? I've been messing with it, but just keep jacking it up, so I am obviously not understanding how you did it. I'm sorry to ask this too.

 

[sheetspread]

Just put quotes around each new term and precede with a comma. If it still doesn't work paste the formula and some data, I'll fix it.

If you want a blank instead of the first one, putting "", in each array first will not work because the formula returns the position (1). An unused character such as ~ that you can delete is another crude workaround.

 

[buggabooed]

Well I'm not sure the answer to my questions above quite yet, but am seeing where when I hit certain items with your original formula, my results start returning incorrectly, and I'm not quite sure why. See below.

CHECKCARD 1230 CHEVRON 00105923 UNIVERSITY X 246113200656517450765132 |CHECKCARD
CHECKCARD 1231 WHICH WICH TATAUGA 51661241615651692165161629651230 |CHECKCARD
CHECKCARD 1231 USPS 4846510231564165149 YELLER 2411361566514232198513 |CHECKCARD
CHECKCARD 1231 SONIC DRIVE IN #4248 YELLER 2451651654561321720071083349 |CHECKCARD
DANCE DES:TUITION ID:xxxxxxxxxx INDN:xxxxxxxxxxx CO ID:XXXXX0xx8 PPD |PAYCHECK
CHECKCARD 0102 MCDONALD'S F10415 TATAUGA 9874654132746516316552 |CHECKCARD
CHECKCARD 0102 SONIC DRIVE IN #8248 YELLER 16516512316498002301654 |CHECKCARD
CHECKCARD 0102 TACO BELL #27820 YELLER 16949464756413210650415 |CHECKCARD
RACETRAC 572 01/03 #00095642654 PURCHASE RACETRAC 572 FT |PURCHASE
Check 7084 |PAYCHECK
Bank of America Credit Card Bill Payment |PAYCHECK
BKOFAMERICA MOBILE 01/06 3564548941 DEPOSIT *MOBILE |DEPOSIT
CHECKCARD 0105 NETFLIX.COM 866-579-7172 CA 98465130189494816065 RECURRING |CHECKCARD
CHECKCARD 0106 Audible 888-283-5051 NJ 2987465131068496152 |CHECKCARD
WAL-MART #9579 01/06 #000984651 PURCHASE WAL-MART #9579 FT |PURCHASE
Check 7085 |PAYCHECK
CITIBANK CREDIT CARD Bill Payment |PAYCHECK
GREAT LAKES Bill Payment |PAYCHECK
STATE FARM INSURANCE COMPANY Bill Payment |PAYCHECK
VERIZON SOUTHWEST Bill Payment |PAYCHECK
GE CAPITAL RETAIL BANK Bill Payment |PAYCHECK


Thank you for your continued help sheetspread!