Forum Discussion
What delimiter shall I use?
That's as Patrick2788 suggested, or if you prefer with formula, practically the same could be
=TRIM( TEXTSPLIT(SUBSTITUTE( <line>, "), (", "),:(" ), ",:") )Above for single row, how to split texts in 2D you may find patterns, few of them. But again, that will be spill, not table.
If you are happy with a 365 formula (rather than text to columns) you could use
=REGEXEXTRACT(@data,"\(\-?\d+\,\s?\-?\d+\)",1)This does not use a separator at all but, rather, extracts any number pairs that match the format irrespective of what separates them.
PeterBartholomew1 , not to care about digits that could be
=REGEXEXTRACT( @data, "(?xim) \( (?>[^()] | (?R) )* \)", 1)or
=REGEXEXTRACT( @data, "(?xim) \( (?:[^()] | (?R) )*\)",1 )but we have spills as result, not structured table.
Thanks SergeiBaklan , any other solution for guys like me :( where in our Excel, REGEX is not available.
Br,
Anupam
That's as Patrick2788 suggested, or if you prefer with formula, practically the same could be
=TRIM( TEXTSPLIT(SUBSTITUTE( <line>, "), (", "),:(" ), ",:") )Above for single row, how to split texts in 2D you may find patterns, few of them. But again, that will be spill, not table.
