RegEx Help

[hasithg]

Hi,

I have a list of accounts which can be in 1 of two formats in the same field:

016123453-134562-AU-AUD
011234568-134242-AU-AUD
000121234512
000112345629

I want to write a regex which will extract it as follows:

Field 1	Field 2	Field 3
016123453	134562	AU-AUD
011234568	134242	AU-AUD
000121234512
000112345629

I wrote the following Regex and parsed the data: ^(\d.+)\-(\d.*)\-.+

This partially worked; however, lines 3 & 4 were blanked out.

Any help is much appreciated!

Thank you!

[Bren_Spill2]

@hasithg - lines 3 & 4 don't fit the pattern for the regex expression you created. You'll need to filter the data to isolate the rows that do fit your expression (e.g. contains"AU") then union back the others - see attached

Also, the expression you wrote isn't returning what you need - I used this: (\d+)\-(\d+)\-(.*)

[DataNath · Accepted solution]

You could also add an optional non-capturing group to check for the second/third capturing groups and only parse them if they're present, like so:

RegEx:

(\d*)(?:-(\d*)-(.*))?

[OTrieger]

If you are looking for a different solution without REGEX using only Formula tool, you can find the position of the 1st - and 2nd - and then using Left and Right functions get the desired output in this way you can finish all what you want with one single formula tool.

[hasithg]

thanks all! appreciate the help!