topic Regex - Extracting data in Alteryx Designer Desktop Discussions

Regex - Extracting data

OOIWJ — Thu, 13 Jun 2024 21:36:11 GMT

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!

Re: Regex - Extracting data

DanielG — Fri, 01 Sep 2023 23:01:21 GMT

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

Re: Regex - Extracting data

Yoshiro_Fujimori — Sat, 02 Sep 2023 00:35:21 GMT

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

Re: Regex - Extracting data

OOIWJ — Sat, 02 Sep 2023 02:04:57 GMT

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.

Re: Regex - Extracting data

agpilo — Mon, 04 Sep 2023 03:06:39 GMT

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

Re: Regex - Extracting data

OOIWJ — Mon, 04 Sep 2023 13:48:57 GMT

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!

Re: Regex - Extracting data

agpilo — Mon, 04 Sep 2023 16:47:59 GMT

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

Re: Regex - Extracting data

OOIWJ — Tue, 05 Sep 2023 14:56:33 GMT

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

Re: Regex - Extracting data

DanielG — Tue, 05 Sep 2023 15:02:33 GMT

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