Regex - Extracting data

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!

Results.png

Account names.png

Regex

Accepted answers

All comments

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!

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703