Extracting text from an attachment filename?

[Andy_Goodacre]

Hello all,

I'm struggling to edit the text in a field referencing an attachment field. I want it to extract a label code from the attachment name. The text looks like…

GAR0027 Southern Vines Cab Sauv 15 F.pdf (https://dl.airtable.com/.attachments/c3a3a85e047021c86d494d90be7dcb64/08f0ad8f/GAR0027SouthernVinesC...), GAR0028 Southern Vines Cab Sauv 15 B.pdf (https://dl.airtable.com/.attachments/034388155febbaac0b80a222437f97f5/3a723729/GAR0028SouthernVinesC...), GAR0027 Southern Vines Cab Sauv 15 F.pdf (https://dl.airtable.com/.attachments/34bf7b45992c17c945927f49bac0365f/f6f2829a/GAR0027SouthernVinesC...), GAR0028 Southern Vines Cab Sauv 15 B.pdf (https://dl.airtable.com/.attachments/6f3bc3598e135c082f1e093c73f0ba11/58802a97/GAR0028SouthernVinesC...), GAR0027 Southern Vines Cab Sauv 15 F.pdf (https://dl.airtable.com/.attachments/8db9deebbb0460d8bb3fa6d367f5bbb5/6e8c7d49/GAR0027SouthernVinesC...), GAR0028 Southern Vines Cab Sauv 15 B.pdf (https://dl.airtable.com/.attachments/c014e46eb5dac1299404377dc58f17d5/46661741/GAR0028SouthernVinesC...)

The code I want to extract is the code starting with GAR, although changes depending on the client. I'm hoping to delete all the character after the first space " " till the next ", ". Not sure if this is possible?

Thanks, Andy

[kuovonne · Accepted solution]

Are there multiple attachments with the same filename structure in a single cell? 

Will something like this work?

REGEX_REPLACE(
  {Notes},
  "(GAR[^ ]+)( .+?\\))(,? ?)",
  "$1$3"
)

[Andy_Goodacre]

Wow, that's amazing!! That is well above my knowledge!! and worked like your example.

Yes, the attachment field is a look up field from another table (sorry forgot to mention that). I thought I could filter the duplicate codes with something like this…

ARRAYUNIQUE(REGEX_REPLACE(

{Unapproved Proofs (from Tasks)},

"(GAR[^ ]+)( .+?\\))(,? ?)",

"$1$3"

))

But it didn't work.

The other part of this, is it possible to change the formula so the GAR element of the code is variable?

Example of some of our other codes; DAR1208, LBF330927, PVW0262, GTV0903

Thank you for your help! I would never have gotten this far.

Andy