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

Regex

Accepted answers

flying008

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

All comments

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())

录制_2024_11_07_14_50_24_824.gif

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

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703