Regex

[BRRLL99]

i have following input , looking for expected output in only one formula tool

DATA

NIPO713|TRANSACTION MANAGEMENT SYSTEM|PAGE     1

RUN|PROD DATE  11/7/2024|SECOUND PRESENT|DATE   11/7/24

TIME 16:48:25

MEMBER ID  0009786|FILE TYPE 002

TRAN AMT|00000007568

PAN|12345678910|CURRENCY CONVERSION 11072024

Expected output : date, id and PAN should come in seperate column using formula tool, for amount column last 2 digits should be always in decimals ex : 7700   should be 77.00

date	id	Amount	card
11/7/24
		75.88
	0009786
			12345678910

[flying008]

Hi, @BRRLL99 

[DATE] = 

IIF(Contains([DATA], 'DATE'), REGEX_Replace([DATA], '^.*\|DATE[ \s]+', ''), Null())

[ID] = 

IIF(Contains([DATA], 'ID'), REGEX_Replace([DATA], '^.*?ID[ \s]+(\d+)\|.*$', '$1'), Null())

[AMOUNT] = 

IIF(Contains([DATA], 'AMT|'), ToNumber(REGEX_Replace([DATA], '^\D+(\d+)$', '$1')) / 100 , Null())

[CARD] =  

IIF(Contains([DATA], 'PAN|'), REGEX_Replace([DATA], '^\w+\|(\d+)\|.*$', '$1'), Null())

[BRRLL99]

@flying008 

for Amount column im getting 0

my data will be 

TRAN AMT|00000007568|MSG REV IND

TRAN AMT|00000009968|MSG REV IND

TRAN AMT|00000007000|MSG REV IND

EXPECTED OUTPUT :
75.68

99.68

70.00

[flying008 · Accepted solution]

IIF(Contains([DATA], 'AMT|'), ToNumber(REGEX_Replace([DATA], '^\D+(\d+)\|?.*$', '$1')) / 100 , Null())

[BRRLL99]

can i get output like this based on ID 

if ID is not null then amount and card should come opposite to ID

date	id	Amount	card
11/7/24
	0009786	75.88	12345678910