Extracting 6 digits Numbers from V_WString

Question

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

IDChannel 11111401015 - DM-NA-DM US Insurance22222DM-NA-DM US Insurance-40101533333401015 - DM-NA-DM Northeast  Insurance44444

401015.000000000000000

Expected outcome

IDChannel Expected outcome11111401015 - DM-NA-DM US Insurance40101522222DM-NA-DM US Insurance-40101540101533333401015 - DM-NA-DM Northeast  Insurance40101544444

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

binu_acs · Answer

@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

Masond3 · Answer

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

* 40
* 41
* 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 ?

binu_acs · Answer

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