Splitting data in one cell across multiple columns

[Mamamia]

Hi,

I need help separating data split in multiple rows in the same cell. Like below

Input:

Payee Reference

S0004023 TSK Property Management Limited ST804 STARKEYS CHARTERED SURVEYORS

S0003906 Sheet Anchor Investments 2 Limited LC095 LCP MANAGEMENT LTD

Desired Output:

Payee ref	Payee Description	Site Code	Payee Name
S0004023	TSK Property Management Limited	ST804	STARKEYS CHARTERED SURVEYORS
S0003906	Sheet Anchor Investments 2 Limited	LC095	LCP MANAGEMENT LTD

I will appreciate help on this. 

Thanks,
Kadmia

[DataNath · Accepted solution]

Hey @Mamamia, is it always the case that the data you want splitting will always be at a linebreak? If so, you can use the Text-to-Columns tool with \n as the delimiter, like so:

[cjaneczko]

Provided the format is the same for every one of your records you can use the REGEX tool and set the configuration to parse into 4 columns using the following REGEX. 

^([A-Z]\d+)\n(.+)\n([A-Z0-9]+)\n(.+)$