How to extract 5, 6, or 7 character codes which begin with a multiple variations of 2 or 3 letters followed by either 3 or 4 digits (e.g. VM4005)?

[Jarlid]

Hi,

I am trying to extract codes which are 5, 6 or 7 characters in length from column B (Name), in which each cell contains a different string of text and the codes are positioned differently in each cell. For example, they could be at the beginning of each cell (in which case it would be fairly easy to extract with the =Left formula), middle of each cell or at the end (in which case it would be fairly easy to extract with the =Right formula).

Each code could begin with the following 2 or 3 letters:

- HT
- PR
- BR
- DG
- GW
- VM
- NO
- TA
- NPD (this is the only prefix that could make the code 7 characters in length)
- NP
- CS
- BE
- CA
- DA
- EX
- CN
- BS
- TR

It is then followed by either 3 or 4 digits (normally beginning with 4).

Please refer to below image of column A and column B. Each cell in column B contains a string of text, in which could contain the 5, 6, or 7 character code I am trying to extract. I have manually entered the code in column A to show what it is I am trying to do, however, I would like to automate this by use of a formula (or formulae) or VBA if that is necessary. Please note that there might not be a code in one of the cells in column B as shown below (I have left the cell empty in column A to show this).

I appreciate any help that you can provide here. Also, please let me know if there is any other information I can provide to make this post read any clearer than it is already. Thank you for your help in advance!

Marketing Code	Name
HT4019	HT4019 -Trade Press Hotel - April
PR4008	TCS July Retainer PR4008
BR4071	BR4071 Hero Christmas Illustra
BR4078	BR4078 Hub Christmas Illustra
HT4010	HT4010-Connoisseur Circle Fee
VM4074	VM4074 - Ashford Trolley Header
VM4075	VM4075 - Swindon Shelftalkers
VM4076	VM4076 - Cheapside Trolley Headers & she
DA4032	DA4032 BA PROFI LINE JUNE
VM4069	VM4069 Outlet FL pos print
VM4069	VM4069 Outlet FL pos print
VM4069	VM4069 Outlet FL pos print
VM4074	VM4074 - Ashford Trolley Header
VM4073	VM4073 Reg Street MM de-rig
EXS027	EXS027 EDP Props Greece
BR4073	BR4073 - Xmas Hub Stills
VM4076	VM4076 - Cheapside Trolley Headers & she
BR4069	BR4069 - Xmas Hero Stills
BR4070	BR4070 - Xmas Hero Moving
CA4011	CA4011 - GQ Father’s Day Gift
VM4075	VM4075 - Swindon Shelftalkers
NO4008	NO4008 - PR RETAINER FEE - JULY
NO4037	NO4037 - COURRIER COST MAY+JUN
NPD5022	2020 O&B LTD Ed 2nd print trial -NPD5022
	Jun20 Prep5082"WGSN Ltd
	Jun20 Prep5079"Walpole PR016
	Jun20 Prep5075"Preferred Hotel
VM4021	VM4021 City imagesSAS & Outlet (contd)4710019679
VM4021	VM4021 City imagesSAS & Outlet (contd)4710019679
VM4010	VM4010 Ampersandconcept 1971 value engineering4710
TA4002	TA4002 COPRACOPRA LUNCH TABLE4710022665
NPD5020	NPD5020 PlastipakHaircare Bottle Print Trial 47100
NPD5018	NPD5018 VPICNY LAMB EDP Resin Pre-Series 471002423
NPD5017	NPD5017 Pusterla MD2021 Box + Mask Trials 2nd Tria
NPD5011	NPD5011 PlastipakLE O&B Bottle Trial4710023241
NPD5009	NPD5009 VPI CNY LOTV Resin Cap Trial Two 471002290
	NPD5008 FC Creative ConceptsFragrance consultant N
	NPD5007 Pusterla MD 2021 Box + Mask Trials First
	NPD5006 VPICNY LOTV Resin Cap Trial One 4710020286
	NPD5005 CurtisVD 2021 Hair Mist Carton Trial471002
	NPD5004 VPIVD 2021 New Fragrance Resin cap pre-ser
	NPD5003 VPIVD 2021 New Fragrance Resin cap second
	NPD5002 VPIVD 2021 New Fragrance Resin cap first t
	NPD5001 Zero Carbon Food Growing Underground One O
	NPD4038 CurtisQVC TSV carton print trial4710023187
	NP4030 NussbaumNeon Amber deo can trial4710018701
	NP4029 POLYPOUCH UK LTD Hand Pouch Trial (includin
	NP4028 AmberleyLabel trial - JJ 4710016951
	NP4026 DekorprintJJ candle trial4710016699
	NP4024 ColeXmas 2020 ribbon samples4710016494
	NP4023 PusterlaXmas 2020 2nd trial4710016289
	NP4022 CosfibelTravel P&M colour trials 471001599
	NP4020 VPIoriental pre series4710011582
	NP4014 PusterlaXmas 2020 1st carton trial471001256
	NP4012 RoystonXmas 2020 BW label trial4710012579
	NP4011 AmberlyXmas 2020 BW label trial4710012523
	NO4042 VASS PR2020 INFLUENCER COLLAB EMMA471002198
	NO4041 VASS PR2020 INFLUENCER COLLAB AMANDA4710021
	NO4008 VASS PRPR RETAINER FEE - JULY4710020184
	CS4028 WIBPack Shots4710022561
	BR4068 Lux DigitalEvergreen Campaign 4710024584
	BR4067 Lux DigitalEvergreen Campaign 4710024396

 

[Jarlid]

Sure, removing the '/b', word boundary flag from the regex pattern should do the trick.

Function xCode(s As String) As String
With CreateObject("VBScript.RegExp")
    .Pattern = "(HT|PR|BR|DG|GW|VM|NO|TA|NPD|NP|CS|BE|CA|DA|EX|CN|BS|TR)\d{3,4}"
    xCode = .Execute(s)(0)
End With
End Function

Thank you very much for your help!

 

[Jarlid]

@Irobbo314 Do you know how to suppress the error '#VALUE!' that the formula returns upon not find that code? Is it possible to just return a blank cell instead?

 

[lrobbo314]

Sure, you can either wrap it in an iferror formula, e.g. =IFERROR(xCode(L12),""), or we can adjust the code. You can replace 'vbNullString' in the code for whatever you want the formula to return if it doesn't find a match.

Function xCode(s As String) As String
With CreateObject("VBScript.RegExp")
    .Pattern = "(HT|PR|BR|DG|GW|VM|NO|TA|NPD|NP|CS|BE|CA|DA|EX|CN|BS|TR)\d{3,4}"
    If .test(s) Then
        xCode = .Execute(s)(0)
    Else
        xCode = vbNullString
    End If
End With
End Function

 

[Jarlid]

Sure, you can either wrap it in an iferror formula, e.g. =IFERROR(xCode(L12),""), or we can adjust the code. You can replace 'vbNullString' in the code for whatever you want the formula to return if it doesn't find a match.

Function xCode(s As String) As String
With CreateObject("VBScript.RegExp")
    .Pattern = "(HT|PR|BR|DG|GW|VM|NO|TA|NPD|NP|CS|BE|CA|DA|EX|CN|BS|TR)\d{3,4}"
    If .test(s) Then
        xCode = .Execute(s)(0)
    Else
        xCode = vbNullString
    End If
End With
End Function

Thank you once again! @Irobbo314

 

[lrobbo314]

───────────────────░█▓▓▓█░▇▆▅▄▃▂
──────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
─────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
──────────░░░───░█▓▓▓▓▓▓█░▇▆▅▄▃▂
─────────░███░──░█▓▓▓▓▓█░▇▆▅▄▃▂
───────░██░░░██░█▓▓▓▓▓█░▇▆▅▄▃▂
──────░█░░█░░░░██▓▓▓▓▓█░▇▆▅▄▃▂
────░██░░█░░░░░░█▓▓▓▓█░▇▆▅▄▃▂
───░█░░░█░░░░░░░██▓▓▓█░▇▆▅▄▃▂
──░█░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░█░░░█░░░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░█░░░░██░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░░█░░░░░██░░░█▓▓▓█░▇▆▅▄▃▂
─░█░█░░░█░░░░░░███▓▓▓▓█░▇▆▅▄▃▂
░█░░░█░░░██░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░█░░░░█████▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░░█░░░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░█░░░░██░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
─░█░█░░░░░████▓▓▓▓██░▇▆▅▄▃▂
─░█░░█░░░░░░░█▓▓██▓█░▇▆▅▄▃▂
──░█░░██░░░██▓▓█▓▓▓█░▇▆▅▄▃▂
───░██░░███▓▓██▓█▓▓█░▇▆▅▄▃▂
────░██▓▓▓███▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓▓▓▓▓▓█░▇▆▅▄▃▂