Looking up Specific Criteria in line item and doing a LookUP

[shahid5788]

[TABLE="width: 1230"]
<tbody>[TR]
[TD="class: xl63, width: 1230"]Can you please assist in helping with my Transaction memo line items below. Please also see ID name and ID number below. I want to be able to have some sort of formula that can look into my Transaction memo and pick up the Vendor ID and its Vendor Name. As you can see though that not all Memo transactions formatting is not the same. And I cannot find any consistency within each line item. The only thing that is consistent is that Vendor ID# contains 10 digits and also the word "AmericanExpress"

Can you please help since I have 100s of line items that are similar to this.[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 1366"]
<tbody>[TR]
[TD]Transaction MEMO[/TD]
[/TR]
[TR]
[TD]ACH CREDIT RECEIVED - Cust ID: PUEBLO Desc: SETTLEMENT Comp Name: AMERICANEXPRESS Comp ID: 1135472120 Batch Discr: PAYMENT DATE 2409 1 SEC: PED Cust Name: EXTRON SOLVT2222353487 Addenda: No Addenda[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]ACH CREDIT RECEIVED - Cust ID: LNID EVENT Desc: SETTLEMENT Comp Name: AMERICAN EXPRESS Comp ID: 1135212147 Batch Discr: PAYMENT DATE 2179 1 SEC: PED Cust Name: LNID EVENT C135483 2457 Addenda: No Addenda[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]ACH CREDIT RECEIVED - Cust ID: 00TUSKOGEE Desc: SETTLEMENT Comp Name: AMERICAN EXPRESS Comp ID: 1136662241 Batch Discr: PAYMENT DATE 1709 1 SEC: CCD Cust Name: TUSKOGEE CIV2252534475 Addenda: No Addenda[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]ACH CREDIT RECEIVED - Cust ID: 000000BCPA Desc: SETTLEMENT Comp Name: AMERICANEXPRESS Comp ID: 2234214367 Batch Discr: PAYMENT DATE 5509 1 SEC: CCD Cust Name: BRANNERT CEN1549547218 Addenda: No Addenda[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]ACH CREDIT RECEIVED - Cust ID: 000000FORD Desc: SETTLEMENT Comp Name: AMERICAN EXPRESS Comp ID: 3674247950 Batch Discr: PAYMENT DATE 2348 1 SEC: CCD Cust Name: FORD IDAHO C687151 5102 Addenda: No Addenda[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 554"]
<tbody>[TR]
[TD]Name[/TD]
[TD][/TD]
[TD]ID#[/TD]
[/TR]
[TR]
[TD]LNID EVENT CENTER[/TD]
[TD][/TD]
[TD]1354832457[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PUEBLO CONVENTION CE[/TD]
[TD][/TD]
[TD]2222353487[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tuskogee Civic Cente[/TD]
[TD][/TD]
[TD]2252534475[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Brannert Center[/TD]
[TD][/TD]
[TD]1549547218[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ICtickets[/TD]
[TD][/TD]
[TD]6871515102[/TD]
[/TR]
</tbody>[/TABLE]








[TABLE="width: 1598"]
<tbody>[TR]
[TD]Transaction MEMO[/TD]
[TD]The Result I would want[/TD]
[/TR]
[TR]
[TD]ACH CREDIT RECEIVED - Cust ID: PUEBLO Desc: SETTLEMENT Comp Name: AMERICANEXPRESS Comp ID: 1135472120 Batch Discr: PAYMENT DATE 2409 1 SEC: PED Cust Name: EXTRON SOLVT2222353487 Addenda: No Addenda[/TD]
[TD]PUEBLO CONVENTION CE[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACH CREDIT RECEIVED - Cust ID: LNID EVENT Desc: SETTLEMENT Comp Name: AMERICAN EXPRESS Comp ID: 1135212147 Batch Discr: PAYMENT DATE 2179 1 SEC: PED Cust Name: LNID EVENT C135483 2457 Addenda: No Addenda[/TD]
[TD]LNID EVENT CENTER[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACH CREDIT RECEIVED - Cust ID: 00TUSKOGEE Desc: SETTLEMENT Comp Name: AMERICAN EXPRESS Comp ID: 1136662241 Batch Discr: PAYMENT DATE 1709 1 SEC: CCD Cust Name: TUSKOGEE CIV2252534475 Addenda: No Addenda[/TD]
[TD]Tuskogee Civic Cente[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACH CREDIT RECEIVED - Cust ID: 000000BCPA Desc: SETTLEMENT Comp Name: AMERICANEXPRESS Comp ID: 2234214367 Batch Discr: PAYMENT DATE 5509 1 SEC: CCD Cust Name: BRANNERT CEN1549547218 Addenda: No Addenda[/TD]
[TD]Brannert Center[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACH CREDIT RECEIVED - Cust ID: 000000FORD Desc: SETTLEMENT Comp Name: AMERICAN EXPRESS Comp ID: 3674247950 Batch Discr: PAYMENT DATE 2348 1 SEC: CCD Cust Name: FORD IDAHO C687151 5102 Addenda: No Addenda[/TD]
[TD]ICtickets[/TD]
[/TR]
</tbody>[/TABLE]

 

[edwin1682006]

For Name, use =MID(A2,SEARCH("Cust Name:",A2)+11,12)
For Cust ID, use =MID(A2,SEARCH("Cust Name:",A2)+23,10)