Parsing Between two set characters

[kevin-lindorfer]

I am trying to parse the following data, and cannot seem to figure out how I can do this with RegEx.

Here are two examples:

Cash Svcs Db/Cr Dep Adjust, Org Dep Amt= .00,

Cash Svcs Db/Cr Dep Adjust, Org Dep Amt= 1,129.86,

There is more data after this, but I want it to pull everything between the "=" and the comma after the amount.

Thank you!

[BenMoss]

Hi!

Do your numeric values have commas in themselves, as with your 2nd option? As this will of course cause some issues.

If not, then an easy option would be to use two text to column tools, the first splitting on the equals sign, and then the 2nd splitting on the comma, which will isolate your numeric value; but of course this only works if your numeric value has no commas in it.

If they do, is there structure to the numbers; do they always contain a decimal place? If so we should be able to make it work with regex, once I have my laptop open i’ll give it a go.

Ben

[NicoleJohnson · Accepted solution]

Try this expression, in parse method if using the RegEx tool (or replace with $1 if using Replace or a RegEx formula):

^.*=\s([0-9,]*\.\d{2}),.*

Between the parentheses, it will give you all numbers & commas it finds after the = (with a space \s), and before the decimal, and will then include the two digits after that \d{2}. Everything from the comma following will be excluded.

Let me know if that works! 

Cheers,

NJ

[kevin-lindorfer]

Hi Ben!

The format always follows: x,xxx.xx, unless it is under $1, in the case of the first example, which shows .00, with no leading zero. I could always remove all commas from the data before starting with regex as well.

Thank you!

[kevin-lindorfer]

This worked great! Thank you!!