Extracting Specific Name within LineItem

[shahid5788]

Hi,

Can you please assist with these Memo line items I have below. I am trying to extract the ID names within each memo line item. Ideally I would like to have some sort of formula that can give me a consistent data. Something like (F248947 TTL Center). Some memo have that space between the ID code then ID name and some for example dont have that space between them (i.e D245070UTAH EVENTS). I would love to have some sort of formula that can give me (ID code space ID name) ex F248947 TTLCEnter.

I have like 30k line items so would love to find a more efficient way in order to get consistent line items. I would still like to have the full line memo line after. (Ex. ACH CREDIT RECEIVED - Cust ID: Ebaya0002584731 Desc: TRANSFER Comp Name: PAYMENTECH Comp ID: 1020401225 SEC: CCD Cust Name: F248947 TTLCenter NewE Addenda: No Addenda)








[TABLE="width: 1944"]
<tbody>[TR]
[TD]MEMO[/TD]
[TD][/TD]
[TD]I need these Names Extracted from this Memo line item[/TD]
[/TR]
[TR]
[TD]ACH CREDIT RECEIVED - Cust ID: Ebaya0002584731 Desc: TRANSFER Comp Name: PAYMENTECH Comp ID: 1020401225 SEC: CCD Cust Name: F248947 TTLCenter NewE Addenda: No Addenda[/TD]
[TD][/TD]
[TD]TTLCenter[/TD]
[/TR]
[TR]
[TD]ACH CREDIT RECEIVED - Cust ID: Ebaya1242577335 Desc: TRANSFER Comp Name: PAYMENTECH Comp ID: 1020401225 SEC: CCD Cust Name: D256179 GOSGATA CYMCAS Addenda: No Addenda[/TD]
[TD][/TD]
[TD]GOSGATA[/TD]
[/TR]
[TR]
[TD]ACH CREDIT RECEIVED - Cust ID: Ebaya0102554425 Desc: TRANSFER Comp Name: PAYMENTECH Comp ID: 1025551225 SEC: CCD Cust Name: E245402CL CENTER Addenda: No Addenda[/TD]
[TD][/TD]
[TD]CL CENTER[/TD]
[/TR]
[TR]
[TD]ACH CREDIT RECEIVED - Cust ID: Ebaya0002572461 Desc: TRANSFER Comp Name: PAYMENTECH Comp ID: 1020401225 SEC: CCD Cust Name: D245070UTAH EVENTS CN

Addenda: No Addenda[/TD]
[TD][/TD]
[TD]UTAH EVENTS[/TD]
[/TR]
[TR]
[TD]ACH DEBIT RECEIVED - Cust ID: Ebaya0002685073 Desc: TRANSFER Comp Name: PAYMENTECH Comp ID: 1047401225 SEC: CCD Cust Name: F245498DonaldLTuckerCi Addenda: No Addenda[/TD]
[TD] [/TD]
[TD]DonaldLTucker[/TD]
[/TR]
[TR]
[TD]ACH CREDIT RECEIVED - Cust ID: Ebaya0002575873 Desc: TRANSFER Comp Name: PAYMENTECH Comp ID: 1020422225 SEC: CCD Cust Name: D245648JUN ENT COMPLE Addenda: No Addenda[/TD]
[TD][/TD]
[TD]JUN ENT[/TD]
[/TR]
[TR]
[TD]ACH CREDIT RECEIVED - Cust ID: Ebaya0002928431 Desc: TRANSFER Comp Name: PAYMENTECH Comp ID: 1020401225 SEC: CCD Cust Name: K265676Puskggee Addenda: No Addenda[/TD]
[TD][/TD]
[TD]Puskggee[/TD]
[/TR]
</tbody>[/TABLE]

 

[LloydFinancials]

The code needs to know when to take 2 or more values such as "UTAH EVENTS" or just 1 "GOSGATA". So you can always tell where the extraction begins, after the 7 character value following "Cust Name:" but wouldn't know where to end. You could extract that 7 character value pretty easily if that helps, or you could extract up to certain number of characters, say 20 for instance.

 

[shahid5788]

Isn't there a way in which it can add a "space" for me after for example F248947. I would ideally would love to find a way in which we can get that consistency. As you can see above some memos have "space" after the 7 digit id code and some don't have that "space" in between those. Would like to make that consistency in which it records a space after that 7 digit id code F248947

 

[LloydFinancials]

Sure, you can add space after the id. What are you thinking? Because we still have the issue of knowing where to end the extraction.

 

[shahid5788]

Umm I think I know no longer need to "extract" anything. If you can just help me with my formatting by adding a space after the id that would be great.

 

[LloydFinancials]

If that ID is always 7 characters you can use: =LEFT(A2,FIND("Cust Name",A2)+17) & " " & RIGHT(A2,LEN(A2)-FIND("Cust Name",A2)-17)