Regex Parse before a string with varying positions

Hello!

I am trying to parse the following data into two fields.

Where the two digits in between colons and before the date appears in one field and where the date/time appears in another. There may be times when the two digits are not there.

I have attempted regex parsing but I am very new to it and cannot get this to work.

Field to Parse	Desired Output Field 1	Desired Output Field 2
413764213-R:1U:14JAN16 00:52:08	1U	14JAN16 00:52:08
414633025-R:1C:11FEB16 00:12:37	1C	11FEB16 00:12:37
414633517-R:15FEB16 00:13:00		15FEB16 00:13:00

Thank you!

Regex

Accepted answers

tom_montpool

@JohnJPS and @Federica_FF have given good ideas.

John hinted at another solution when he said "splitting with colons" -- you could also use the "Text to Columns" tool and set the delimiter to the colon character.

If you set the number of columns to 3, it won't split your date/time into separate fields, as long as you leave the Extra Columns setting at the default (Leave Extra in Last Field).

If the two digits are not always there, you could add a filter before the Text to Columns where you filter on Regex_CountMatches([FieldToParse],":")==3.

All comments

JohnJPS

If the date is always in that 16 character format, it is easy to find that:

Right([FieldToParse],16)

This portion of the string can also be removed using "Replace"... and we can also replace up through the first colon by finding its index using Findstring... all in all, the following will give you the middle string that remains:

Replace(
  Replace(
    Replace([FieldToParse],Right([FieldToParse],16),""),
    Left([FieldToParse],FindString([FieldToParse],":")+1),"")
  ,":","")

I'm sure there's a clever way to do this by splitting on colons and doing IIF on the count of matches... actually this will work:

IIF(REGEX_CountMatches([FieldToParse],":") == 3,"",
    GetWord(Replace([FieldToParse],":"," "),1))

... which basically knows it's empty if there are only three colons, otherwise it replaces colons with spaces and grabs the second "word" thus created.

Federica_FF

Hi,

if you want to go directly with a regex expression, you can try the regex tool, parse method with this pattern:

\d+-\w:(?:(\d\w?):)?(.+)

\ d+ --> one or more digits

- --> minus character

\ w --> 1 letter

: --> 1 semicolon

(?: ( \ d \ w ) : ) ? --> 1 digit + 1 letter + semicolon and the whole group \d\w: can be there or not (the question mark means 0 or 1 occurency, both conditions are considered)

( . + ) --> whathever comes after

tom_montpool

@JohnJPS and @Federica_FF have given good ideas.

John hinted at another solution when he said "splitting with colons" -- you could also use the "Text to Columns" tool and set the delimiter to the colon character.

If you set the number of columns to 3, it won't split your date/time into separate fields, as long as you leave the Extra Columns setting at the default (Leave Extra in Last Field).

If the two digits are not always there, you could add a filter before the Text to Columns where you filter on Regex_CountMatches([FieldToParse],":")==3.

KHOGAN_TD

This worked great! Thank you to all who posted.

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335