Find String within a cell

[yzhang316]

Hi,

I have the following string of transaction data in this cell under column name Details

Cust: ABC :Cust /TrnTypN: Internal Invoice :TrnTypN /Trns#: 1832942 :Trns# /BGC: :BGC /DSeq#: :DSeq# /BatN: PA Internal Invoices :BatN /Proj#: 52136849 :Proj# /Rec_OU: ABCDF :Rec_OU /CCProj#: 55123698 :CCProj#

I need to find the Transaction#, in this case it is 1832942.  

My method is: Right(left([Details],FindString("Trns#: ",[Details])+13),7) 

However, I am getting the wrong result even thought on my excel worksheet will find the current number.  Not sure what I did wrong there...

Please help!

[The_Data_Loop]

Your going to want to use a Regex parse in this case. Someone else may be able to chime in white the actual code.  

[tcroberts · Accepted solution]

I would approach this using RegEx.

I have a few questions however,

Are the transaction strings always in this format roughly? Can you rely on the string "Trns#:" always being directly before what you're looking for, and on :Trans# being directly after it?

If so, you could use a formula tool with two expressions in it:

The first shuld create a new field [Out] (or whatever you want), and have the following:

REGEX_Replace([Details], ''.*Trns#:, '')

and then:

REGEX_REPLACE([Out], ':Trns#.*','')'

Here's a picture.

Spoiler

You'll want to clean up the new field with a Data Cleaning Tool afterwards to avoid trailing and leading whitespace (or you can play with the REGEX to make it work).

Let me know if this helps,

Cheers!

[yzhang316]

Yay!  Thank you sir!