RegEx with mix of trailing spaces, number and letters

[Ewbkm]

Need help to parse the original data to 4 different columns?  Is it possible to use one RegEx to solve it?  

Original Data	Expected RegEx Parse Results
Account Combo	Account No	Sequence No	Sub Account	Currency
1001-600534-100-VTR-1014616      -AUD	1001-600534-100		-VTR-1014616	AUD
1001-600534-101-VTR-1014619      -USD	1001-600534-101		-VTR-1014619	USD
1001-100128-100-VTR-1012817H     -HKD	1001-100128-100		-VTR-1012817H	HKD
1001-100915-100-VTR-1014460      -USD	1001-100915-100		-VTR-1014460	USD
1001-100128-100-VTR-1012819H     -HKD	1001-100128-100		-VTR-1012819H	HKD
1001-100874-500--2-USD	1001-100874-500	2		USD
1001-100874-510--2-HKD	1001-100874-510	2		HKD
1001-100874-588--2-CNH	1001-100874-588	2		CNH

[binuacs]

@Ewbkm One way of doing this

[Ewbkm]

Thanks.  

I don't need the two dashes in front of the RegExOut2 (squence ## ) and need to get rid of the dash - in front of VTR in RegExOut3.  Can you help me more on this?  

[TimN · Accepted solution]

Similar approach

[apathetichell]

4 new fields in a formula tool:

1)

regex_replace([Account Combo],"^(\d+-\d+-\d+)(.*)","$1")

2)

if length(regex_replace([Account Combo],"^(\d+-\d+-\d+--)(\d+)(.*)","$2"))=1 then regex_replace([Account Combo],"^(\d+-\d+-\d+--)(\d+)(.*)","$2") else "" endif

3)

if regex_match([Account Combo],"^.*-\w{3}-\d+\w*.*") then regex_replace([Account Combo],"^(.*-)(\w{3}-\d+\w*)(.*)","$2") else "" endif

4)

right([Account Combo],3)

[flying008]

Hi, @Ewbkm 

FYI.

^([\d-]{15})(--(\d{1,})|-([[:alnum:]-]*))[- \s]+([[:alpha:]]+?)$

BTW, if you want to get the first '-' before 'VTR' in field [Sub Account], the use the expression to parse:

Spoiler

^([\d-]{15})(--(\d{1,})|([[:alnum:]-]*))[- \s]+([[:alpha:]]+?)$

Account Combo	Account No	Sequence No	Sub Account	Currency
1001-600534-100-VTR-1014616      -AUD	1001-600534-100		-VTR-1014616	AUD
1001-600534-101-VTR-1014619      -USD	1001-600534-101		-VTR-1014619	USD
1001-100128-100-VTR-1012817H     -HKD	1001-100128-100		-VTR-1012817H	HKD
1001-100915-100-VTR-1014460      -USD	1001-100915-100		-VTR-1014460	USD
1001-100128-100-VTR-1012819H     -HKD	1001-100128-100		-VTR-1012819H	HKD
1001-100874-500--2-USD	1001-100874-500	2		USD
1001-100874-510--2-HKD	1001-100874-510	2		HKD
1001-100874-588--2-CNH	1001-100874-588	2		CNH

******

If can help you get your want, please mark it as a solution and give a like for more share.