Extraction of invoice number from a string column, using matching rules

Question

Hello,

Could you, please help me with the situation below:

How can I, extract the invoice number, which starts with S0 and keep only the invoice number - Ex. S0001 00008761.

I should mention that i can find more invoice numbers, into a line and sometime the invoice number might not respect all the number length.

Description053000EFWERW4T71EUR2 000070006546945071025 CCCFACTURE N S0001 000073600430003741GHH004971EUR2 00007000239FH4507102500071025FACTURE N S00SHHTG 00000000 000000023673HHTRF2YUU026970530003741004971EUR2 005YHY023945281025 CCC1902499959 PAGO DE FACTURA N S0001-000085590530003741004971EUR2 0J63700023945271025 CCC1902499900 PAAA FACTURA N S0001 000087610430003741004971EUR2 000070002359G14082500140825RGLT FACT S00SHYHH OTTT B 00000000 0000000049493DTRF009991080430003741004971EUR2 0000700023193H6032500170325S0001-0000596APPPP SHIPPING 4 00000000 0000000396404ES06539319B8401

Book4.xlsx

jrlindem · Accepted Answer

Regex will be your friend for this, but there is inconsistency in your pattern example.  If you are literally looking for "S####_########" then the following works:

But as you can see that does not help with situations like:

So if you can use Regex to identify the pattern, you should be able to adapt it.  If you need further help.  Can you provide the expected output from each line and myself or others can further help.  -Jay