Need to segregate one column into multiple column based on condition

[Gsiva3]

Hello Team,

I have attached the sample input.

My query is to convert one column into 4 columns . I have data in input column as "ZZ98** ABCDEF ************* AAAAA UUUU" 

I want to split this above mentioned cell into 4 columns like 

1st Column - ZZ98

2nd Column - ABCDEF

3rd Column - AAAAA

4th Column - UUUU

I have attached the sample input and desired output here.

Please support me on this.

Input

ZZ98** ABCDEF  *************  AAAAA  UUUU

ZZ98INOT WORKING               GGGGG

ZZ98** SAMPLE INPUT  *************  JJJJJ

ZZ98***GHIJKLMNOP  *************  BBBBB  VVVV

ZZ98** SAMPLE OUTPUT  *************  KKKKK

ZZ98***KEEP THE DATA  *************  LLLLL

ZZ98***QSRTUVWXYZASDFG *************  CCCCC  WWWW

ZZ98***DON’T KEEP THE DATA *************  MMMMM

ZZ98***ERROR IN DATA ************* NNNNN

ZZ98***DO NOT CHANGE ************* DDDDD  XXXX

ZZ98***   PLEASE CHANGE      *****  EEEEE          01_09_1985

ZZ98ITS WORKING             FFFFF     +YYYYY          01_09_1985

ZZ98***   MARK THE DATA      *****  PPPPP

ZZ98   CONTROL NOT AVAILABLE      *****  OOOOO

Output 1	Output 2	Output 3	Output 4
ZZ98	ABCDEF	AAAAA	UUUUU
ZZ98	INOT WORKING	GGGGG
ZZ98	SAMPLE INPUT	JJJJJ
ZZ98	GHIJKLMNOP	BBBBB	VVVVV
ZZ98	SAMPLE OUTPUT	KKKKK
ZZ98	KEEP THE DATA	LLLLL
ZZ98	QSRTUVWXYZASDFG	CCCCC	WWWWW
ZZ98	DON'T KEEP THE DATA	MMMMM
ZZ98	ERROR IN DATA	NNNNN
ZZ98	DO NOT CHANGE	DDDDD	XXXXX
ZZ98	PLEASE CHANGE	EEEEE
ZZ98	ITS WORKING	FFFFF	YYYYY
ZZ98	MARK THE DATA	PPPPP
ZZ98	CONTROL NOT AVAILABLE	OOOOO

[Shifty · Accepted solution]

Hey @Gsiva3!

RegEx is not my forte but I hate seeing a question go unanswered so I've dusted off my RegEx skills and attached a workflow that seems to get you the output you desire.  One thing to note is that my Output 4 varies from yours but I think that's because the Input column occasionally only has 4 characters in the final section to be output (e.g. line 3).

Let me know if that helps!

[Shifty]

@Gsiva3 - I realised my garble was unclear regarding the number of characters (Input vs Output4) so hopefully this will help illustrate what I mean. 😊

[Gsiva3]

Hi @Shifty 

The workflow is working fine!!!

Thanks for the support!

[Shifty]

Hey @Gsiva3  - that's awesome.  Delighted to have helped!

[Gsiva3]

Hi @Shifty 

Sorry to ask you again.

Just now noticed.

I have one more input format which is as follows.

"ZZ98** ABCDEF QWSDE ************* AA AA UUUU".

In this case when I am using the workflow what you have sent, in output column no.3 I am getting only "AA" whereas my required output is

"AA AA" in output column no.3

Again attached the sample files.

Could you please help this time.

[Shifty]

Oooh OK. Let me take another look.

[Shifty · Accepted solution]

OK this was trickier but try the attached . 😊

[Shifty]

Whoops - forgot to tag you!  @Gsiva3 

Let me know if it works as you hoped.

[Gsiva3]

Thanks @Shifty 

Its working Fine!!!