RegEx - Identify/split data into columns

Question

Hi,

I am new to RegEx and I have the below data which needs to be split into different columns. The challenge I am facing is that the Account description (2nd column) is not in a consistent format (i.e., the number of strings/words in the relevant description column are not consistent).

Could someone please help me with a solution to this? Thank you in advance!

RegEx.yxmd

ShankerV · Accepted Answer

Hi @RajatK

Please find the below output you are expecting.

Note: The workflow used to achieve the solution is attached which can be downloaded to see how the solution works.

If you believe your problem has been resolved. Please mark helpful answers as a solution so that future users with the same problem can find them more easily!!!!

Many thanks

Shanker V

ShankerV · Accepted Answer

@RajatK

Please rename the column name in Regex tool to get the desired column name.

Note: The workflow used to achieve the solution is attached which can be downloaded to see how the solution works.

If you believe your problem has been resolved. Please mark helpful answers as a solution so that future users with the same problem can find them more easily!!!!

Many thanks

Shanker V

ChrisTX · Accepted Answer

This is likely not the most elegant or efficient solution, but it seems to work.

Brute force:

^(\d+)(\s)(([A-Za-z0-9\s\-/])+)(\s)(([$\,\d]+\.[$\d]+)+)(\s)(([$\,\d]+\.[$\d]+)+)(\s)(([$\,\d]+\.[$\d]+)+)(\s)(([$\,\d]+\.[$\d]+)+)$

Other experts may be able to provide better RegEx.  The website regex101: build, test, and debug regex helps.

Chris