Okay easy Regex question. See example below.
FIELD_1 is a key field used to join data. The base pattern for the key is exemplified in Record #1.
The base pattern is ^\d[A-Z]\-\d{7}[A-Z]{1,3}\-[A-Z]{1}.
Records #2 and #3 add a 4 digit project code. But, the project code should be appended to the base key.
The pattern should be ^\d[A-Z]\-\d{7}[A-Z]{1,3}\-[A-Z]{1}\-.{4}.
Records #2 and 3 are wrong. They read, ^\d[A-Z]\-\d{7}[A-Z]{1,3}\-.{4}\-[A-Z]{1}
How do I correct.
I tried using a Regex Tool set to Parse: (^.*?)(\-.{4})(\-Q) with the output set to $1 $3 $2. That did not work.
| RecordID | FIELD_1 | DESIRED OUTCOME |
| 1 | 4020X-99-R-0602143P-Q | 4020X-99-R-0602143P-Q |
| 2 | 9113X-99-R-0303138P-2039-Q | 9113X-99-R-0303138P-Q-2039 |
| 3 | 9113X-99-R-1203110FS-78C9-Q | 9113X-99-R-1203110FS-Q-78C9 |
