Split cols first 2 comma and last 2 comma seperately into cols

[Skarthika]

Hi all,

i have a text as below i need to split first 2 occurances and last 2 occurances of "," into separate columns and all data in between those comms should be in a single column. This is just an example. i have 20 occurances of commas in my data where i need to split first 6 values and last 13 values. 

Data

1, 2, dfg, wet, ssssssshf, 67, 58

1, 6, wer, sdf, 34, 66

Your help is much appreciated.

[Qiu]

@Skarthika 
I am having hard time to understand your intention here,
can you provide the sample output for larger sample input?

[KGT]

Brute force method is to T2C (Text to Columns) all fields, either vertically, or horizontally followed by Transpose, then label the lines, then select the elements. I've shown a couple methods in the attached depending on your data.

The other option, that is technically better, but more of a "this use case only" solution, is to find the character number of the second comma and the second last comma, then substring everything in-between. I can't remember the easy way to grab the second comma, and I'm on 2022.3, so don't have the 2023 formula additions.

[Skarthika]

Thanks for the inputs im looking for an output similar to this.

C1	C2	C3	C4	C5
1	2	dfg wet  ssssssshf	67	58
1	6	wer sdf	34	66

[PangHC · Accepted solution]

@Skarthika 
i guess you mean this

^(.+?),(.+?),(.+),(.+?),(.+?)$

[Skarthika]

Theat worked, thanks a lot PangHC

[RJ_1988]

Alternatively you can use a combination of text to columns and reverse string function in formula tool and finally Concatenate them.