Extracting 6 digits Numbers from V_WString

[Masond3]

Team, 

Hope all is well/ 

I may of asked this question before, ( apologies if i have) 

I have a Column called "Channel" Whilst looking at the values within the  "Channel" column it has become transparent there is no standardization and format is all over the place. 

All i want to do is  extract the 6-digit number found within channel

Example Data

ID	Channel
11111	401015 - DM-NA-DM US Insurance
22222	DM-NA-DM US Insurance-401015
33333	401015 - DM-NA-DM Northeast  Insurance
44444	401015.000000000000000

Expected outcome 

ID	Channel	Expected outcome
11111	401015 - DM-NA-DM US Insurance	401015
22222	DM-NA-DM US Insurance-401015	401015
33333	401015 - DM-NA-DM Northeast  Insurance	401015
44444	401015.000000000000000	401015

Unfortunately i cant attached my workfow (but below is a screen shot of my flow  but looks too simple. 

1st Data Cleanse : channel (Remove "leading and trailing white space, tabs, line breaks and duplicates and letters) 

Regex : #|_|(\d+)\D+(\d+)?\D?(\d+)?

2nd Data Cleanse : channel (Remove "leading and trailing white space, tabs, line breaks and duplicates and Punctuation) 

Text to columns  :  Split to rows 

Filter on length  = 6 

 

[binuacs]

@Masond3 does your output always start with 40 or 4?

[Masond3]

@binuacs  So looks like it always starts with a 4, The first two digits can always change i see the following patterns 

1. 40
2. 41
3. 42

Going forward lets say the channels codes change from starting with 4, and it  becomes 5, is it best to hard code the value in or be dynamic ? 

[binuacs]

@Masond3 It is always good to give the exact pattern to the regex to get the correct result, if you just mention picking 6-digits, the regex will pick any 6 digits it matched, the result might not be your correct answer, If you specify the 6 digits number you wanted to pick starts with 4 or 5 it is easy for the regex to check only those numbers