REGEX TO EXTRACT 6 DIGIT

Question

I would like to extract 6 digit number from each row , each should start with reporting and rows have trailing spaces

DATA   REPORTING FOR:       1000035909 ABCD BANK MIPPL        INTERNATIONAL SETTLEMENT SERVICE      PROC DATE:10AUG25   REPORTING FOR:       100003570 ABCD BANK MIPPL        INTERNATIONAL SETTLEMENT SERVICE      PROC DATE:10AUG26   REPORTING FOR:       1000075909 BIN 459930 ABCD BANK MIPPL        INTERNATIONAL SETTLEMENT SERVICE      PROC DATE:10AUG27   REPORTING FOR:       1000035909 458830 ABCD BANK MIPPL        INTERNATIONAL SETTLEMENT SERVICE      PROC DATE:10AUG28   REPORTING FOR:       1000055909 ABCD BANK MIPPL        INTERNATIONAL SETTLEMENT SERVICE      PROC DATE:10AUG29   REPORTING FOR:       1000088909 SUMM 453390 ABCD BANK MIPPL        INTERNATIONAL SETTLEMENT SERVICE      PROC DATE:10AUG30   REPORTING FOR:       100003009 ABCD BANK MIPPL        INTERNATIONAL SETTLEMENT SERVICE      PROC DATE:10AUG31   REPORTING FOR:       1000035909 ABCD BANK 451290 SUMM MIPPL        INTERNATIONAL SETTLEMENT SERVICE      PROC DATE:10AUG32   REPORTING FOR:       1000035909 ABCD BANK MIPPL        INTERNATIONAL SETTLEMENT SERVICE      PROC DATE:10AUG33

Expected output:

null

459930
458830
null
453390
null

451290
null

GMG0241 · Accepted Answer

In that case, use REGEX_REPLACE([Column],".*?\s(\d{6})\s.*?","$1")

Kottapally_Ramesh · Accepted Answer

Attached is the workflow to extract 6-digit number from the provided input data.

flying008 · Accepted Answer

Hi, @BRRLL99

FYI.

^.+?:[\w\s]+?\s(\d{6})\s.+$