Hi @sriniprad08,
If you're asking how to come up with a regex solution-- that's a great question! Everyone thinks about it slightly differently, but I've outlined part of my thought process:
- I learned regex with the brief lessons on regexone.com and the Alteryx interactive lessons
- I practice using regex with my own data and within Alteryx challenges (61, 59, 47, 40, 20, 13 to name a few). Practice is the best way to get comfortable writing regex.
- When I get stuck writing a regex formula in Alteryx (which is often!), I instead type it into rubular.com because it shows me exactly what the capture groups will be. I prefer that editing environment for regex because it's easier to see the effects of any changes.
- When I'm writing regex, I look for patterns. Regex is awesome for working with patterns. In your data, the patterns were tricky! Here are some of the ones I saw
- The totals were inconsistently placed around the data, but (!) the total was always the largest and last number
- Most (but not all) of the dollar amounts had a $ in front of them. I used regex to capture all strings that had a $ and any attached numbers with no spaces.
- Dates had a consistent format so were the easiest part with the regex. I looked for a string as follows: numberCharacter{1 or 2 repetitions} forwardSlashCharacter{single repetition} numberCharacter{1 or 2 repetitions} forwardSlashCharacter{single repetition} numberCharacter{2 to 4 repetitions}
- The invoice numbers were the hardest part because the placement was not consistent and the type of string desired was not consistent. The main patterns I saw were that it was sort of close to the word "invoice", contained at least some numbers, and was not a dollar amount. So I removed all the dollar amounts in the strings before attempting to parse the invoice numbers.
So my main recommendation when using regex is to look for patterns! Location within string at large, type of characters, repeated characters, etc. All of these can be utilized by regex.
