RegEX Challenge

[Newbee3]

I am in need again of the alteryx community.

Problem: I am having data played into alteryx from a PDF. Consequently I am presented with a string of information from the whole row (see below tab 'start data') which I want to bring into separate columns (see below tab 'goal format'). I tried to go with the "text to columns" function and the delimiters "\s\t" which didn't work out what got me thinking I might have to use RegEx on that one.

start data

CH.047'397'801'0 SMIM/BCV 20         342'000.000         101.130CHF         345'864.60         345'864.60

CH.011'559'548'72 EIB 22 EMTN         38'250'000.000             106.900CHF        40'889'250.00          40'889'250.00

CH.013'549'352'36.25 LIONS INVES      21'000'000.000             100.000EUR        21'000'000.00          22'799'070.00

CH.011'862'411'0BARCLAYS 16 FRN       16'560'000.000              98.850EUR        16'369'560.00          17'771'940.20

CH.048'182'357'014.95 BSKT/JPMS 2         1'000'000.000         106.160CHF         1'061'600.00         1'061'600.00

CH.003'965'118'4ENERGIEDIENST HLD         440.000         32.800CHF         14'432.00         14'432.00

CH.000'162'471'4CPH CHEM PAP H N-          3'273.000         79.200CHF         259'221.60         259'221.60

LU.025'263'375'4MUL LY DAX (DR) A         45.000         124.060EUR         5'582.70         6'060.96

GB.00B'421'JZ6'60.5 UK TSY I-L 50         37'400'000.000         257.091GBP         96'152'179.86         123'257'479.36

goal format

CH.047'397'801'0	SMIM/BCV 20	242'000.00	201.130	CHF	855'864.60	395'864.60
CH.011'559'548'7	2 EIB 22 EMTN	38'233'000.00	106.912	CHF	110'889'250.00	110'889'250.00
CH.013'549'352'3	6.25 LIONS INVES	21'080'000.00	180.000	EUR	26'000'001.00	22'809'070.00
CH.011'862'411'0	BARCLAYS 16 FRN	6'560'000.00	98.850	EUR	16'365'560.00	37'771'940.20
CH.048'182'357'0	14.95 BSKT/JPMS 2	1'000'080.00	122.160	CHF	1'000'600.00	1'000'600.00
CH.003'965'118'4	ENERGIEDIENST HLD	340.00	22.800	CHF	34'432.00	34'432.00
CH.000'162'471'4	CPH CHEM PAP H N-	3'273.00	7.200	CHF	25'921.60	25'921.60
LU.025'263'375'4	MUL LY DAX (DR) A	40.00	124.060	EUR	5'582.70	6'060.96
GB.00B'421'JZ6'6	0.5 UK TSY I-L 50	33'400'000.00	997.091	GBP	96'152'179.86	123'257'479.36

Looking forward to be hearing from you guys. Thank you already upfront for your help.

Newbee3

[atcodedog05]

Hi @Newbee3 

Here is a workflow for the task.

Regex: Prase mode

(.{16})\s?(.*)\s([\w\.']*)\s([\w\.']*)\s([\w\.']*)\s([\w\.']*)

Output:

Config:

Hope this helps 🙂 Feel to ask if you have any questions

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[mceleavey]

Hi @Newbee3 ,

I've attached a workflow, I hope this helps.

Regex is my friend.

[afv2688]

Hello @Newbee3 ,

How does this look like to you? It's not clean but it works!

If you have any question about the logic let me know 🙂

Regards

[PhilipMannering]

I think @atcodedog05 nailed it. See my method attached.

[Newbee3]

Gentlemen @PhilipMannering @afv2688 @mceleavey @atcodedog05 

thank you very much for your fast response. Apologies for promoting you again, but I think we need to tweak the current solutions slightly to have the final solution fitting the problem.

1. Problem

while copying over the data from the different excels to ensure displaying a variety of cases I must have incorporated a mistake. Please find the data again as per below table.

2. Problem

It appear that the solutions sent over are lacking two things: a) the currency type (CHF, USD, EUR) is not separated from the number and not put into a separate column. b) some solutions ignore the fact that the first 'item' always must have 13 'digits/characters' - anything afterwards (even if a number) belongs to the name and consequently into the second column (not the case for @atcodedog05 's solution).

CH.011'559'548'72 EIB 22 EMTN         18'250'000.000             106.900CHF        40'889'250.00          40'889'250.00

CH.011'862'411'0BARCLAYS 16 FRN       16'330'000.000              98.850EUR        16'369'560.00          17'771'940.20

CH.011'866'825'72.125 BNG 29          11'050'000.000             122.800CHF        14'306'200.00          14'306'200.00

CH.011'954'263'42.00 EIB 35            9'750'000.000             131.000CHF         6'222'500.00           6'222'500.00

CH.012'009'639'81.75 DTEBAHN FIN     822'030'000.000             101.020CHF       183'886'706.00        183'886'706.00

CH.012'295'537'72.25 DBG 21            7'630'000.000             102.010CHF         7'365'122.00           7'365'122.00

CH.012'420'553'22.25 NWB 21           1'825'000.000             102.980CHF        12'177'385.00          12'177'385.00

CH.012'473'990'22.5 CADES 25          20'825'001.000             116.850CHF        24'334'012.50          24'334'012.50

CH.012'506'226'22.5 CRH 21            70'385'004.000             103.530CHF        75'975'490.50          75'975'490.50

CH.012'651'604'32.625 ING BANK 21      9'885'008.000             102.860CHF         9'612'267.00           9'612'267.00

thank you again for your support

[atcodedog05 · Accepted solution]

Hi @Newbee3 

Here is the updated workflow.

Please take a look and let me know🙂

[atcodedog05]

Happy to help 🙂 @Newbee3 

Cheers and Happy Analyzing 😀

Feel free to reach out if you face any issues 🙂

[BS_THE_ANALYST]

Old post but another solution. Nice problem!