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)?

J

Jarlid

New Member
Joined
Aug 10, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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 CodeName
HT4019HT4019 -Trade Press Hotel - April
PR4008TCS July Retainer PR4008
BR4071BR4071 Hero Christmas Illustra
BR4078BR4078 Hub Christmas Illustra
HT4010HT4010-Connoisseur Circle Fee
VM4074VM4074 - Ashford Trolley Header
VM4075VM4075 - Swindon Shelftalkers
VM4076VM4076 - Cheapside Trolley Headers & she
DA4032DA4032 BA PROFI LINE JUNE
VM4069VM4069 Outlet FL pos print
VM4069VM4069 Outlet FL pos print
VM4069VM4069 Outlet FL pos print
VM4074VM4074 - Ashford Trolley Header
VM4073VM4073 Reg Street MM de-rig
EXS027EXS027 EDP Props Greece
BR4073BR4073 - Xmas Hub Stills
VM4076VM4076 - Cheapside Trolley Headers & she
BR4069BR4069 - Xmas Hero Stills
BR4070BR4070 - Xmas Hero Moving
CA4011CA4011 - GQ Father’s Day Gift
VM4075VM4075 - Swindon Shelftalkers
NO4008NO4008 - PR RETAINER FEE - JULY
NO4037NO4037 - COURRIER COST MAY+JUN
NPD50222020 O&B LTD Ed 2nd print trial -NPD5022
Jun20 Prep5082"WGSN Ltd
Jun20 Prep5079"Walpole PR016
Jun20 Prep5075"Preferred Hotel
VM4021VM4021 City imagesSAS & Outlet (contd)4710019679
VM4021VM4021 City imagesSAS & Outlet (contd)4710019679
VM4010VM4010 Ampersandconcept 1971 value engineering4710
TA4002TA4002 COPRACOPRA LUNCH TABLE4710022665
NPD5020NPD5020 PlastipakHaircare Bottle Print Trial 47100
NPD5018NPD5018 VPICNY LAMB EDP Resin Pre-Series 471002423
NPD5017NPD5017 Pusterla MD2021 Box + Mask Trials 2nd Tria
NPD5011NPD5011 PlastipakLE O&B Bottle Trial4710023241
NPD5009NPD5009 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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
How about this?

New Document (2).xlsx
AB
1NameVBA
2HT4019 -Trade Press Hotel - AprilHT4019
3TCS July Retainer PR4008PR4008
4BR4071 Hero Christmas IllustraBR4071
5BR4078 Hub Christmas IllustraBR4078
6HT4010-Connoisseur Circle FeeHT4010
7VM4074 - Ashford Trolley HeaderVM4074
8VM4075 - Swindon ShelftalkersVM4075
9VM4076 - Cheapside Trolley Headers & sheVM4076
10DA4032 BA PROFI LINE JUNEDA4032
11VM4069 Outlet FL pos printVM4069
12VM4069 Outlet FL pos printVM4069
13VM4069 Outlet FL pos printVM4069
14VM4074 - Ashford Trolley HeaderVM4074
15VM4073 Reg Street MM de-rigVM4073
16EXS027 EDP Props GreeceEXS027
17BR4073 - Xmas Hub StillsBR4073
18VM4076 - Cheapside Trolley Headers & sheVM4076
19BR4069 - Xmas Hero StillsBR4069
20BR4070 - Xmas Hero MovingBR4070
21CA4011 - GQ Father’s Day GiftCA4011
22VM4075 - Swindon ShelftalkersVM4075
23NO4008 - PR RETAINER FEE - JULYNO4008
24NO4037 - COURRIER COST MAY+JUNNO4037
252020 O&B LTD Ed 2nd print trial -NPD5022NPD5022
26Jun20 Prep5082"WGSN Ltd#VALUE!
27Jun20 Prep5079"Walpole PR016PR016
28Jun20 Prep5075"Preferred Hotel#VALUE!
29VM4021 City imagesSAS & Outlet (contd)4710019679VM4021
30VM4021 City imagesSAS & Outlet (contd)4710019679VM4021
31VM4010 Ampersandconcept 1971 value engineering4710VM4010
32TA4002 COPRACOPRA LUNCH TABLE4710022665TA4002
33NPD5020 PlastipakHaircare Bottle Print Trial 47100NPD5020
34NPD5018 VPICNY LAMB EDP Resin Pre-Series 471002423NPD5018
35NPD5017 Pusterla MD2021 Box + Mask Trials 2nd TriaNPD5017
36NPD5011 PlastipakLE O&B Bottle Trial4710023241NPD5011
37NPD5009 VPI CNY LOTV Resin Cap Trial Two 471002290NPD5009
38NPD5008 FC Creative ConceptsFragrance consultant NNPD5008
39NPD5007 Pusterla MD 2021 Box + Mask Trials FirstNPD5007
40NPD5006 VPICNY LOTV Resin Cap Trial One 4710020286NPD5006
41NPD5005 CurtisVD 2021 Hair Mist Carton Trial471002NPD5005
42NPD5004 VPIVD 2021 New Fragrance Resin cap pre-serNPD5004
43NPD5003 VPIVD 2021 New Fragrance Resin cap secondNPD5003
44NPD5002 VPIVD 2021 New Fragrance Resin cap first tNPD5002
45NPD5001 Zero Carbon Food Growing Underground One ONPD5001
46NPD4038 CurtisQVC TSV carton print trial4710023187NPD4038
47NP4030 NussbaumNeon Amber deo can trial4710018701NP4030
48NP4029 POLYPOUCH UK LTD Hand Pouch Trial (includinNP4029
49NP4028 AmberleyLabel trial - JJ 4710016951NP4028
50NP4026 DekorprintJJ candle trial4710016699NP4026
51NP4024 ColeXmas 2020 ribbon samples4710016494NP4024
52NP4023 PusterlaXmas 2020 2nd trial4710016289NP4023
53NP4022 CosfibelTravel P&M colour trials 471001599NP4022
54NP4020 VPIoriental pre series4710011582NP4020
55NP4014 PusterlaXmas 2020 1st carton trial471001256NP4014
56NP4012 RoystonXmas 2020 BW label trial4710012579NP4012
57NP4011 AmberlyXmas 2020 BW label trial4710012523NP4011
58NO4042 VASS PR2020 INFLUENCER COLLAB EMMA471002198NO4042
59NO4041 VASS PR2020 INFLUENCER COLLAB AMANDA4710021NO4041
60NO4008 VASS PRPR RETAINER FEE - JULY4710020184NO4008
61CS4028 WIBPack Shots4710022561CS4028
62BR4068 Lux DigitalEvergreen Campaign 4710024584BR4068
63BR4067 Lux DigitalEvergreen Campaign 4710024396BR4067
Sheet11
Cell Formulas
RangeFormula
B2:B63B2=xCode(A2)


VBA Code: 
Function xCode(s As String) As String
With CreateObject("VBScript.RegExp")
    .Pattern = "\b[A-Za-z]{2,3}\d{3,4}\b"
    xCode = .Execute(s)(0)
End With
End Function
 
Upvote 0
Just noticed an issue. Updated code below.

VBA Code: 
Function xCode(s As String) As String
With CreateObject("VBScript.RegExp")
    .Pattern = "\b(HT|PR|BR|DG|GW|VM|NO|TA|NPD|NP|CS|BE|CA|DA|EX|CN|BS|TR)\d{3,4}\b"
    xCode = .Execute(s)(0)
End With
End Function
 
Upvote 0
lrobbo314 said:
Just noticed an issue. Updated code below.

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

Thanks for you very prompt response Irobbo314. I have copied and pasted your code in to a VBA module, having made sure I saved the file with the format .xlsm. When I type =xCode the formula prompt does appear but the formula comes up with a #VALUE! error. Not sure what I am doing wrong?
 
Upvote 0
Not sure why you're getting that error. The #VALUE error will come up if it can't find a match, but it's working on this end. Sounds like a dumb question, but I need to ask... you are passing in the cell value to the custom function right? e.g. =xCode(A2)
 
Upvote 0
sandy666 said:
maybe ([A-Z]{2,3})(\d{3,4}\s) and Trim
Click to expand...

I went with something like this originally, but the OP says he has those specific values to match for the letters. The original was extracting EXS027 from 'EXS027 EDP Props Greece' when it wasn't supposed to be.
 
Upvote 0
lrobbo314 said:
I went with something like this originally, but the OP says he has those specific values to match for the letters. The original was extracting EXS027 from 'EXS027 EDP Props Greece' when it wasn't supposed to be.
Click to expand...
sure
 
Upvote 0
lrobbo314 said:
Not sure why you're getting that error. The #VALUE error will come up if it can't find a match, but it's working on this end. Sounds like a dumb question, but I need to ask... you are passing in the cell value to the custom function right? e.g. =xCode(A2)
Click to expand...

I can confirm that I have tried again and this code works! The only error I receive however is when the the code is adjoined to other text e.g. =xCode(PR3039CEWCEW0) doesn't return PR3039. Is there a way to change the code so there isn't a need to have a space in between, thank you!
 
Upvote 0
Sure, removing the '/b', word boundary flag from the regex pattern should do the trick.

VBA Code: 
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
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,635
Messages
6,125,945
Members
449,275
Latest member
jacob_mcbride

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top