RegEx Expression Help

[RCern]

I am just starting to dabble in RegEx expressions, but I am running into a wall with this.

I have a TXT file that has all data in one column.  I am trying to parse out the data to 4 columns (Customer Name, Assoc Name, Product, Amount).   I can parse out the first product but not sure on what to do next.  Do I add to the current regex expression or keep adding new Regex tools to keep parsing out the data.  I attached the test workflow I am playing around with.  Any guidance would be tremendously appreciated.

I ideally would like this to be the end result:

Test Customer 1    John Smith     A&L   $107.93

Test Customer 1    John Smith    CDC  $4000.02

Test Customer 2    Joy Ranger    VPX    $2.45

Test Customer 2    Joy Ranger    LDL     $45.35

Test Customer 2    Joy Ranger    CPI     $3.93

Test Customer 2    Joy Ranger    DK       $3.45

Test Customer 2    Joy Ranger    A&L      $109

[binuacs]

@RCern one way of doing this

[flying008]

Hi, @RCern 

You can Tokenize by expression :

(?:^|\s{2,})(.+?)(?=\s{2,}|$)

[RCern]

Thank you both!   

@flying008    If I had a cell with this string " 10/04  GROCERYSTORE ATHENS GA  1,2489.45"  What would the RegEx expression be to only extract out the 1,2489.45 into its own column?  Could it also accommodate other amounts such as 63.34  or 7.00?

[flying008 · Accepted solution]

Hi, @RCern 

FYI.

[RCern]

Thank you @flying008 !   Any chance you can walk me through what that expression is doing?  Also, would it solve for if a "cent" was left off of the extract?  For example, if one of the data lines had  45.4  cents without the trailing "0"?

[CoG]

The Regex above does the following (REGEX: "^.*\s" -> replaced with "")

1. ^ - start from the beginning of the text

2. .* - the '.' character is a wildcard that matches any character. the '*' tells the system to look for 0 or more of the '.' (in a greedy fashion aka as many characters as it possibly can)

3. \s - this is an escaped character that stands for a space ' '

4. The Regex will thus take all characters until the very last space in the string and replace with nothing, which will leave behind only the final text item regardless of trailing 0's, decimals, commas, etc. Just beware, if your string has trailing spaces, then this Regex will output an empty string.

Regex is best learned through practice, so keep trying new things and see how the output changes, just be careful because small changes can have a large impact on behavior. Pro Tip: Be as explicit as possible when writing Regex.

Hope this helps and Happy Solving!

[flying008]

Hi, @RCern 

for example, you can upload input data and output of your want.