How to extract text patterns from string value?

[BalazsNy]

Hi all,

I have a database with a various length description column, which contains a product ID somewhere in it (even at the beginning, middle or end of the description). These product IDs follow the following pattern: 3 letters + 7 numbers (e.g: ADZ0001234, XET0002341).

Do you have any idea, how can I extract these product IDs?

Thanks in advance for the tips.

[Ahmedx]

Is this what you are looking for?

[BalazsNy]

Unfortunately, the issue is a bit more complex. You can find my examples replied to your below comment ▼

[Ahmedx · Accepted solution]

pls try this

[BalazsNy]

Dear @Ahmedx!

Thank you very much. Your formula brilliantly works 🙂

[BalazsNy]

Thanks for the tips, but I have already tried to solve with these methods, but couldn't figure out a proper formula, because the position of product ID varies field-by-field & non-digit/digit splitting neither worked because sometimes these description fields also contain quarter-related strings like "Q1".

[Ahmedx]

show some example and what result you want

[BalazsNy]

Here are 3 example fields & expected results:

Q3 2022 P&T SI&O MRT ABZ0001235 Monitoring Run Tooling CH --> ABZ0001235
CBR0002134 for PT Development Services 2022 (Q4) -->  CBR0002134 
4 PCS MIDDLEWARE 2022-Q3 --> ""

As you can see, the productID always begins with 3 letters & followed by 7 numbers, which can be anywhere within the string. Sometimes the string doesn't contain any productID, then the result should be empty.

[Daniel29195]

@BalazsNy 

i understand that you dont have  only this pattern ADZ0001234 ?  am  i right ? 

if so, 

can you give some examples of how your productid could be ? 

[amitchandak]

@BalazsNy , In power query try split by position

https://learn.microsoft.com/en-us/power-query/split-columns-positions

Or split by Non Digit to Digit

Split Column By Digit to Non-Digit & Non-Digit to Digit: https://youtu.be/tY4Yk1crS9s