IF Statement to return Value

millerfam916

New Member
Joined
May 6, 2019
Messages
3
My excel workbook was built by a previous employee and I am not having any luck updating the excel formula to return the value needed. The formula is used to scan bank transaction descriptions and identify store activity. The formula below currently determines if the activity is cash or credit. However, Paymentech is not built into the formula. Can someone help me change the formula so that the data populates? The second formula pulls out the merchant id, which I will bold the merch ID in the data. Then there is a data tab in the workbook that lists the merchant Ids and their store. The column with the store values will work once I can get the Type and Merch ID to read Paymentech activity. (I changed numbers and names as it is bank activity)

Data: PAYMENTECH DEPOSIT 150508 6300294 Mistys - 0624 1020401225 6300123 R00000091003959987359

Type: =IF(E20="","",IF(E20="Y",IF(C20<>"","CASH",IF(OR(LEFT(D20,3)="AME",LEFT(D20,3)="BAN",LEFT(D20,3)="MER",LEFT(D20,3)="TRA",LEFT(D20,7)="5/3 BAN"),"CREDIT",IF(LEFT(D20,4)="EMPS","CREDIT",IF(OR(LEFT(D20,6)="STRIPE",LEFT(D20,6)="SHOPIF"),"SHOPIFY",IF(LEFT(D20,4)="TSYS","CREDIT","OTHER"))))),""))

Merch ID: =IF(F20="CREDIT",IF(LEFT(D20,3)="AME",MID(D20,36,10),IF(LEFT(D20,3)="BAN",MID(D20,FIND("TOT DEP ",D20)+15,15),IF(LEFT(D20,3)="MER",MID(D20,LEN("MERCHANT BANKCD DEPOSIT 151201 ")+1,12),IF(LEFT(D20,3)="TRA",MID(D20,34,14),IF(LEFT(D20,7)="5/3 BAN",MID(D20,FIND("SYS COMB. DEP. ",D20)+22,13),IF(LEFT(D20,4)="EMPS",MID(D20,21,12),IF(LEFT(D20,4)="TSYS",MID(D20,FIND("XXXX",D20),14),""))))))),"")
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

millerfam916

New Member
Joined
May 6, 2019
Messages
3
I want the type to pull read Credit with paymentech in the data string and the merch id to pull
6300123.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,517
Messages
5,523,349
Members
409,512
Latest member
Exceldoktor

This Week's Hot Topics

Top