Question

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

  • 6 April 2021
  • 4 replies
  • 506 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 +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

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

@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.

Userlevel 7
Badge +10

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