I want to use REGEX to extract the "Account names" and "Account numbers" into two columns, appearing on the same row, like what was shown in the "Results" image, but I want them appearing on the same row. The Account names have more than 2 formats, see "Account names" image. How can I do so using the REGEX function? Thank you!
@OOIWJ - more info is needed, like what does the incoming data look like?
Hi @OOIWJ
Assuming that Col [F2] is the input data and Col [AccountNo.] and [AccountName] are the output data,
the formula to differentiate the column to store the input data would be as below.
If you have more complicated patterns, check this site to edit RegEx with trial and error. https://regex101.com/
Good luck.
Formula
AccountNo = IF REGEX_Match([Data], "\d{4}-\d{3}-\d{2}") THEN [Data] ELSE Null() ENDIF
AccountName = IF REGEX_Match([Data], "\d{4}-\d{3}-\d{2}") THEN Null() ELSE [Data] ENDIF
Output
Data | AccountNo | AccountName |
0102-000-00 | 0102-000-00 | |
CASH - UB - OPERATING | CASH - UB - OPERATING |
YEs, the output was created by me using REGEX, but that is still not what I wanted. I want the output to appear on the same row.
Hi bro
Could you describe the desire output for this example? Confirm if this Is what you expect
Actual Output:
|Account No | Account Name
| 99-99-99 | NULL
| NULL. | XX-XX-XXXXX
Expected Output:
|Account No | Account Name
| 99-99-99 | XX-XX-XXXXX
Let me know please
Yes, the expected output is what I want and I managed to get the result already.
Anyway, I am currently in the process of cleaning a General Ledger Excel file using Alteryx. Would you be interested in reviewing the Excel file and helping me out? I am more than happy to have a zoom with you as I have deadline to meet.
Cheers!
Good news! I'm interested on how you solved it. Ok, we can chat about your project
kindly provide me with your email so that i can send you the excel file, thanks
OK. I am unsubscribing from this one. Sharing your general ledger with an unknown person on the internet scares me to death. Good luck.