REGEX Remove everything after the second comma

I would like to remove everything after the second comma in a string. Im thinking a regex formula would be the way to go, however im not that great at it. Im working with a large data set, so some cells my not even have more than 1 comma.

Column A | Column B

X | stv-47474, ftv-78765, stv-47474, ftv-78765

I want it to remove all the duplicates after the second comma

Column A | Column B

X | stv-47474, ftv-78765

Developer Tools

Regex

Accepted answers

OllieClarke

Hi @fitch1892

Try this formula

REGEX_REPLACE([Field],'([^,]*,[^,]*).*','$1')

It's very similar to @IraWatt's approach, but it also works on strings which have <2 commas

Hope that helps,

Ollie

OllieClarke

@fitch1892

To explain, we are capturing 0 or more non commas

[^,]*

then a comma then 0 or more non commas

,[^,]*

then I'm matching the rest of the string (0 or more of anything)

.*

and then replacing all of that with the captured group

$1

This means that if there are < commas, we leave the string alone as the RegEx doesn't match, but if there are >=2 commas, then we just keep up to the second comma

IraWatt

@OllieClarke great point I've updated my regex to:

([^,]*,*[^,]*)

This should allow for any combination of ,'s

@fitch1892 I think @OllieClarke's great explanation covers my regex as well but feel free to ask if you have any questions

All comments

IraWatt

Hey @fitch1892,

This regex can do the trick:

([^,]*,*[^,]*)

Any questions or issues please ask
HTH!
Ira

Workflow.yxmd

IraWatt

@fitch1892 if you want to learn more about Regex checkout the community interactive videos on it Interactive Lessons - Alteryx Community 😄

OllieClarke

Hi @fitch1892

Try this formula

REGEX_REPLACE([Field],'([^,]*,[^,]*).*','$1')

It's very similar to @IraWatt's approach, but it also works on strings which have <2 commas

Hope that helps,

Ollie

OllieClarke

@fitch1892

To explain, we are capturing 0 or more non commas

[^,]*

then a comma then 0 or more non commas

,[^,]*

then I'm matching the rest of the string (0 or more of anything)

.*

and then replacing all of that with the captured group

$1

This means that if there are < commas, we leave the string alone as the RegEx doesn't match, but if there are >=2 commas, then we just keep up to the second comma