Formula that pulls only set of characters that contain numbers

Hey Guys!

I have a ton of listing titles for products I sell and I am trying to pull the part numbers out into a separate column. Is there a way to pull only the groups of words or parts numbers that contain a number? Please see example below:


240356406 - Sears Kenmore Refrigerator Door Bin Shelf Bucket White Replacement
Frigidaire Refrigerator Door Bin Shelf Bucket Clear AH430122 EA430122 891214
240356409 - Frigidaire Refrigerator Door Bin Shelf Bucket White Replacement
Sears Kenmore Refrigerator Door Bin Shelf Bucket White AH430121 EA430121 891213
240356402 - Frigidaire Refrigerator Door Bin Shelf Bucket Clear Replacement
240356414 - Sears Kenmore Refrigerator Door Bin Shelf Bucket Clear Replacement


I would like a separate column to pull out the following (because these sets of characters have numbers in them)


240356406
AH430122 EA430122 891214
240356409
AH430121 EA430121 891213
240356402
240356414


This would save me a ton of time. Thanks in advance for any assistance you can provide!

Originally Posted by CK76

Is there case where alphabet will be after the number portion? Ex. 430121AB

If not, it's relatively easy to do with PowerQuery/Get & Transform.

If it could... then you can still do it via PowerQuery but will require writing custom function. Probably much easier using VBA with RegEx in that case.

Yes, the part number could end with letters as well. 430121AB is a possibility! Not sure if it matters or not but the part number could also have a "-" so 4301-21AB could also be possible. Thanks!

Originally Posted by CK76

Yes that would matter quite a bit. Can you upload sample workbook that has more sample (about 50) that represent your actual data set?

To upload, use "Go Advanced" button and follow "Manage Attachments" hyperlink. It will launch new tab/window for managing uploads.

Please see attached! Thanks!

Originally Posted by CK76

Try something like below.

Please Login or Register  to view this content.

Thank you Thank you Thank you! This works great! I have a couple listings titles that include 3 letter words in caps that it seems to be pulling as a part number. Example "PTC" and "ERP" .. These are brand names and will not be connected to a part number. I guess I can fun a find and replace on those after though. Thanks again for your help with this! Saved me a ton of time.

Thank you guys so much!!!