Output Query

[clarrock]

Hi,

Trying to make each class and ID separate with numbers with the brackets inside the columns 1 2 and 3. This is my output that i'm trying to reach at : 

ID	Class	1	2	3
56893	abc	107055660008	206108730048	N/A
56893	DCF	N/A	N/A	N/A
56893	Cui	N/A	N/A	N/A
56893	Car-	N/A	N/A	N/A
26895	UDF	N/A	N/A	N/A
26895	PDF	N/A	N/A	N/A
26895	CAR	N/A	N/A	N/A
2556	UDF	NO-0115488	N/A	UB-chr
123	UDF	NO-0391098	N/A	N/A
123	GDC	NO-0391098	N/A	N/A
123	abc	107055660008	N/A	N/A
123	DCF	N/A	N/A	N/A

Heres my input : Is this possible

ID	1	2	3
56893	abc(PVI:107055660008,CVI:107055660008),DCF(N/A),Cui(N/A),Car-(N/A)	abc(PVI:206108730048,CVI:206108730048),DCF(N/A),Cui(N/A),Car-(N/A)	abc(N/A),DCF(N/A),Cui(N/A),Car-(N/A)
26895	UDF(N/A),PDF(N/A),CAR(N/A),	UDF(N/A),PDF(N/A),CAR(N/A),	UDF(N/A),PDF(N/A),CAR(N/A),
2556	UDF(PCVI:NO-0115488)	UDF(N/A)	UDF(PCVI:UB-chr)
123	UDF(PVI:NO-0391098),GDC(PVI:NO-0391098),abc(PVI:107055660008),DCF(N/A)	UDF(N/A),GDC(N/A),abc(N/A),DCF(N/A)	UDF(N/A),GDC(N/A),abc(N/A),DCF(N/A)

[jamielaird · Accepted solution]

Hi @clarrock ,

Here's an approach that gets the result you are looking for:

Spoiler

 You might want to do a bit more work with the Regex if you want to make this able to handle a wider range of values.

[clarrock]

@jamielaird this was really helpful 🙂 , but i'm facing a certain problem - the Class CVI is being included in the output sadly, is there a way to do add record 2 "CVI:107055660008" that value back into record 1 "abc(PVI:107055660008" so it becomes "abc(PVI:107055660008 CVI:107055660008)"

and this would happen if it sees CVI in the first 3 letters of Value ie adding back to previous record

When using the formula tool Class = Left([Value],3) it takes only 3 letters out. Is it possible to have like a logic if its "abc(" then its splits the class

because some of input values include "team-qwe(PVI:107000)"

Please help