REGEX Remove everything after the second comma

[fitch1892]

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

[IraWatt]

Hey @fitch1892,

This regex can do the trick:

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

Any questions or issues please ask :)
HTH!
Ira

[IraWatt]

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

[OllieClarke · Accepted solution]

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 · Accepted solution]

@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 · Accepted solution]

@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 :D 

[carl_cimino]

using some sample data how would you do the following?  I've tried a number of variations of this to no avail.  any help appreciated

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

1. return the number between the 2nd and 3rd period?
2. return everything after the third period?

Data

data.page.182.deviceId.deviceClass

data.page.140.lastRequestTime

data.page.29.hasBufferLicense

data.page.99.addon.0.productList.entitledProduct.0.product.primaryKeys.name

data.page.43.userIdentifier.primaryKeys.lastName

data.page.117.name

data.page.104.userIdentifier.primaryKeys.emailAddress

data.page.197.name

[OllieClarke]

Hey @carl_cimino 

I've attached 2 approaches below - the easiest way is just to use text to columns, but if you really want to RegEx it then parsing 

.+?\..+?\.(\d+)\.(.+)

will also work

Hope that helps,

Ollie

[carl_cimino]

This works great I was able to remove a couple of tools from my flow because of it!  Thank you!