Extracting based off of a portion of the field

jstromain

Forums

I did some searching on here and could not find what I'm looking for. I have a bank account which has two different debit cards associated with it. On the bank statement, the description of the purchase will look similar to "Walmart Toledo, OH 1234" with 1234 being the last four of the debit card. This desctiption column was imported as "Character". I'm trying to extract all transactions which contain 1234 in the description column. How do I accomplish this? Thanks

Brian Element Wed, 10/11/2017 - 18:57

Hi jstromain,

There are many ways you could create an extraction equation.  Here is one example for you:

@Strip(@right(MY_FIELD, 4)) == "1234"

In this case I use the @strip to remove any extra spaces that there might be and then I use the @right to get the last 4 right characters and compare them to 1234.  So this equation will extract all records that end in 1234 for that field.

Hope that helps.

Brian

Steven Luciani Thu, 10/12/2017 - 09:25

Hi jstromain,

As Brian said above there are many ways to do this. My suggestion would be:

@justnumberstrailing(description) = 1234

If you need to extract more than one debit card's purchases into a file then you could use:

 

@match(@justnumberstrailing(description), 1234,5678,1135) and so on for as many last 4's you needed to extract.

 

 

jstromain Thu, 10/12/2017 - 10:22

Thanks Bryan and Steven. I used Bryan's technique because I did not see yours at first.  How would I go about doing a similar function but for a specific word in the text such as "Walmart"?

Steven Luciani Thu, 10/12/2017 - 14:46

If you want to search for more than one term using an equation I would recommend the @REGEXPR function. Unlike @isini, this function is case sensitive so you have to nest in an @upper or @lower when using it. See example below:

@regexpr(@lower(description), "walmart|target|sears") 

This allows you to isolate all three stores with one equation. The vertical lines in the equation are a pipe (|) symbol found above the backslash key so shift+\. It's important that the letters you type between the quotes match the case of the function used with your character field. Notice I used @lower and all my letters are lower case between the quotes.