Parse

[aparna0208]

Hi,

I have the below column in a file that I'm trying to parse which includes different values and format. I need to split it into 2 columns as indicated below.

I tried regex but not getting expected output because there is no one pattern that I use to parse

Thanks in advance

Status

555 SUSPECTED FRAUD 113 NO RECORD

789 STOLEN CARD(C) 113 NO RECORD

UNKNOWN 113 NO RECORD

789 STOLEN CARD(C)
555 SUSPECTED FRAUD

555 SUSPECTED FRAUD 896 NOT SUCCESSFUL

UNKNOWN

Output Example

Col 1                                                           Col 2

555 SUSPECTED FRAUD                         113 NO RECORD

789 STOLEN CARD(C)                              113 NO RECORD

UNKNOWN                                                113 NO RECORD

789 STOLEN CARD(C)

555 SUSPECTED FRAUD

555 SUSPECTED FRAUD                          896 NOT SUCCESSFUL

UNKNOWN

[DataNath · Accepted solution]

@aparna0208, is the pattern here just that sometimes there is a second 3-digit code referring to an error? If so, there's a few approaches. Something like this ought to work:

Col 1:

REGEX_Replace([Status], '(.+)\s\d{3}.+', '$1')

Col 2:

Trim(Replace([Status], [Col 1], ''))

[aparna0208]

@DataNath It worked. Thank you so much for your help:)