Question

Formatting Text Filename- Find and Trim Trigger Word with Multiple Possible Trigger Words

  • 6 April 2021
  • 4 replies
  • 533 views

Hello friends! I’m struggling with what I feel like should be pretty obvious but I keep hitting a wall. See below for a breakdown of the issue:

 

So basically, we have a bunch of digital assets with a standard filenaming convention. It goes Type_AssetName_Stage_Artist_Version, where Stage could be Rough, Clean or Color. For example:

PR_Shovel_Rough_Bob_v01

CH_Fancy_Lady_Clean_Joe_v03

BG_Ext_Dentist_Office_Color_Karen_v02

 

I want to make a Formatter step that takes these filenames, whatever they are, and trim off the _Stage and everything after it. So PR_Shovel_Rough_Bob_v01 would become PR_Shovel, CH_Fancy_Lady_Clean_Joe_v03 > CH_Fancy_Lady, BG_Ext_Dentist_Office_Color_Karen_v02 > BG_Ext_Dentist_Office_Color

 

I know how to do this using formula functions in either Google Sheets or Airtable. My formula finds either ‘_rough’, ‘_clean’ or ‘_color’ then trims it and everything after using FIND and MID. However, Zapier doesn’t seem to have the functions I need under Formatter > Numbers > Spreadsheet Style Formula (only number functions there, nothing like MID or FIND) or Formatter > Text > Split Text (great but requires a separate step for each possible Stage which gets really clunky).

 

I’d really like to avoid having to put the filename through Google Sheets for it to output what I need. The ultimate dream would be for Formatter > Text > Split Text to allow multiple options under Separator but that doesn’t seem to be how it works. I would also be happy if the Formatter > Numbers > Spreadsheet Style Formula had text functions. But even so, I feel like this isn’t that complicated and should be able to be achieved in Zapier with one step. Any suggestions on how I could achieve this would be great! Thank you!

 


This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

4 replies

Userlevel 7
Badge +10

@beneficialnematode 
Just checking in to see if you still need help with this? 

Userlevel 7
Badge +14

@beneficialnematode

File Naming Convention Format: Type_AssetName_Stage_Artist_Version

 

If there are only 3 Stage Types, then that’s manageable to use via 3 Zaps with Filters or 1 Zap with Paths, which then have a Formatter > Text > Split step to isolate the desired part of the file name.

 

Otherwise, try to further standardize the file naming conventions to only have 4 underscore for the different parts of the file name, which can then be used via the Formatter to Split out the desired part of the file name.

Thanks for the response Tony! I’ll try messing around with Extract Pattern. Maybe it’s time for me to learn some Python/regular expressions business!

Re: adding columns to Airtable or Google Sheets, I should have been more clear with where the filenames are coming from. The filenames are from jpgs I upload to Dropbox. So the eventual goal is upload a jpg to Dropbox, which triggers the Zap. Then Zapier formats the jpg filename to match a column I already have set up in Airtable (which is the bg_ext_dentist_office format). If it’s able to find the matching record, the screenshot gets uploaded to an Attachment field for that record. It’s getting Zapier to spit out the filename how I want it (without a separate step for each stage) so it matches Airtable that I’m struggling with.

Userlevel 7
Badge +14

Hi @beneficialnematode 

This may be a bit tricky since you’re dealing with the actual values not the underlying formula.

If the values are coming from GSheets or Airtable, why not add another column with a formula that returns the desired value to use in the Zap?

Otherwise, try Formatter > Text > Extract Pattern

More advanced would be to use a custom Code step: https://zapier.com/apps/code/help