Regex - Extracting data

[OOIWJ]

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!

[DanielG]

@OOIWJ  - more info is needed, like what does the incoming data look like?

[Yoshiro_Fujimori]

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

[OOIWJ]

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.

[agpilo]

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

[OOIWJ]

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! 

[agpilo]

Good news! I'm interested on how you solved it. Ok, we can chat about your project

[OOIWJ]

kindly provide me with your email so that i can send you the excel file, thanks

[DanielG]

OK.  I am unsubscribing from this one.  Sharing your general ledger with an unknown person on the internet scares me to death.  Good luck.