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

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.

Regex

Accepted answers

AncientPandaman

@Skarthika
i guess you mean this

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

Screenshot 2025-05-07 133720.png

All comments

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.