Regex parse string that may contain optional characters

Question

Hi. I am trying to use the RegEx Tool to parse the following source data:

Doe, JSmith, K::abc123::500Chan, L::abc567::500

The pattern of data is that 1) it may contain the substring "::abcXXX::500", 2) if it does, only the "XXX" part will vary from one record to another. And I'd like to parse the data into 2 columns like this:

Doe, J Smith, Kabc123Chan, Labc567

I used this expression in the RegEx Tool
(.*?)\:\:(\w*)\:\:

and was able to correctly parse the last 2 records. But for the first record, it's giving Nulls for both columns. How should I modify the expression to make that substring "optional"?

Thanks in advance!

BenMoss · Accepted Answer

Perhaps the simplest way would be to replace instances of :: with a single delimiter such as | and then use the text-to-columns tool to do a split, using this new delimiter, to three columns, this will give us a name column for every line, a second column for lines with the additional suffix which would be your abc123 or abc567 and then a final column '500' which you can simply remove.

Check out my solution attached.

Ben