Extract the last two values from rows in text file with RegEx

[45179902]

I have a text file with data like these:

ISP-123456    2    2    2    2

ASI-123213    2    3    1    5

PSI-123233    3    2    3    7

SRI-213232          1    4    9

SIR-122323    1    2   24   0

Suppose I want the last two values from each row (2 2, 1 5, 3 7, and 4 9), how can I achieve this? Note that these rows do not have the same number of column, but the values are always in the correct position. I'm thinking about a regular expression that will trim data starting with the second whitespace counting from the end of each row.

Update:

Thank you for the responses! But they remind me that I should be more clear with my question. Sorry for the incomplete example. I have added row five. As can be seen, the values can have more than one digit (24 0). In this case I still want the last two values of each row.

[atcodedog05]

Hi @45179902 

Provided they are in the same position this regex should work. After this just get the values from last 2 columns.

Workflow:

Hope this helps : )

[messi007]

@45179902,

You can use right function:

Regards,

[45179902]

@atcodedog05 Wow, this must be magic. Even better than what I thought! I was having a hard time dealing with those blank values in the rows. I'll give it a try!

[45179902]

@messi007 Hi, thank you for the response. I've updated my example to make my expectation clearer. It is possible that the values can have more than one digit. In this case, the Right() function may not be adequate. Do you know how to deal with these multi-digit values?

[45179902]

Hi @atcodedog05  I've updated my example to include multi-digit values. Does your solution work for these as well?

[atcodedog05]

Hi @45179902 

Here is the updated workflow. This allows up to 3 digits more digits then that workflow breaks.

Hope this helps : )

[OllieClarke · Accepted solution]

Hey @45179902 If you just want the last 2 numbers per row then this RegEx in parse should get you there:

.*\s+(\S+)\s+(\S+)

Hope that helps,

Ollie

edit: changed the RegEx to parse any non-space characters rather than just numbers

[45179902]

@atcodedog05   Thank you for the updated solution. Since it's not possible to determine how many digits there will be, I think your solution would be great if you can integrate @OllieClarke's solution. I like the way your solution deals with those blank entries. That could be useful as the values are still aligned in the correct columns after splitting up.

[atcodedog05]

Hi @45179902 

@OllieClarke assumes the last 2 numbers are not blanks. My solution will work on position and extract the last 2 numbers even if either of the last 2 is blank. I tried splitting on length of each column but that didn't work 😅

If there were no blanks the solution would have been a lot easier and I would have ended up with a similar solution as @OllieClarke 😅

Anyways Happy to help : )

I will give it a try and will post if I am able to come up with something 🙂

Cheers and have a nice day!