title: Is there a way to trim out observations with a known format?
body: <p>I am working with a database with 40+ variables. Each case has a unique identifier for its property. Some of these identifiers have been inputted into the address variable. </p>
  
  <p>The identifiers can only take the following formats:</p>
  
  <pre><code>NA123456 - First letter constant - N, 1 Letter A-K, Numbers 1-9
  SA123456 - First 2 letters constant - SA, 6 Numbers 0-9
  MABC1234 - First letter constant - M, 3 Letters A-Z, 4 Numbers 0-9
  QABC1234 - First letter constant - Q, 3 Letters A-Z, 4 Numbers 0-9
  WABC1234 - First letter constant - W, 3 Letters A-Z, 4 Numbers 1-9
  TABC1234 - First letter constant - T, 3 Letters A-Z, 4 Numbers 1-9
  3ABCD123 - First number constant - 3, 3 Letters A-Z, 3 Numbers 1-9
  </code></pre>
  
  <p>I am unsure how to trim out the unique identifiers from the address text they are in without creating a lookup table and using left_join. The lookup table would need to be updated constantly making it very cumbersome. </p>
  
  <p>I haven't been able to find an example of this sort of thing yet. I may have missed something though.  </p>
  
  <p>My data looks like this: </p>
  
  <pre><code>Property                        Address               `Aa reference`
     &lt;chr&gt;                           &lt;chr&gt;                 &lt;lgl&gt;         
   1 PIC: 3WABG086                   260 SPRINGHURST ROAD  NA            
   2 PIC: 35PSR217                   1350 RIVER ROAD       NA            
   3 PIC# NH244157                   1038 QUONDONG ROAD    NA            
   4 PIC: 3GMUF425                   70 DIGBY ROAD         NA            
   5 PIC# 3GMUF425                   70 DIGBY ROAD         NA            
   6 PIC QTIWW0626                   REMOLEA               NA            
   7 PIC#EBWSE235                    BOX 191               NA            
   8 PIC #3WLKM019                   198 MONTGOMERY ROAD   NA            
   9 PIC # 3BWMM021                  149 ANDERSONS ROAD    NA            
  10 PIC: 3WCGN034                   WERRIBEE              NA            
  11 GARANGULA PIC: NH630488         PO BOX 84             NA            
  12 GARANGULA PIC: NH630488         PO BOX 84             NA            
  13 PIC: 3GMTL320                   2980 GLENELG HIGHWAY  NA            
  14 GREENSLOPES PIC: MJKE0261       914 WEST KENTISH ROAD NA            
  15 PIC: WFZB3246                   859 PFEIFFER ROAD     NA            
  16 PIC: WFAY3549                   34605 ALBANY HIGHWAY  NA            
  17 PIC: 3CEXK044                   2244 LAVERS HILL ROAD NA            
  18 PIC: QGWW0462                   ELDERFIELD            NA            
  19 PIC: 3WCGN034                   WERRIBEE              NA            
  20 KAYA DORPER &amp; WHITE DORPER STUD PIC: WABN0262         NA            
  21 SPOTTSWOOD                      PIC QKDR0078          NA            
  22 COOMBOONA HOLSTEINS             PIC 3SPSR217          NA            
  23 ROSEVALE                        PIC: QKEV0169         NA            
  24 NA                              PIC 3EGON009          NA            
  25 NA                              PIC WFKPO316          NA            
  26 IVADENE                         PIC 3WANP0T1          NA            
  27 NA                              PIC ND225813          NA            
  28 HEAVENLY VALLEY FARMS           PIC #NF538645         NA            
  29 C/- CED WISE AB CENTRE          PIC: QCST0158         NA            
  30 GARANGULA                       PIC # NH630488        NA
  </code></pre>
  
  <p>The clean data would end up with the unique identifers in the <code>aa reference</code> column and wouldn't overwrite the observations that have the data in the correct variable with NA. </p>
  
  <p>Many thanks in advance for your help.</p>