I’m building a formula in Airtable that combines multiple fields to create file names automatically. The problem I’m running into is that when some fields are empty, I end up with double underscores in my final result.
When the SubtitleTrack field is blank, I get something like CompanyName_Video_HD_S01__v2_mp4.mov with those ugly double underscores. Is there a way to make the formula skip the separator when a field is empty? Any help would be great!
This only adds the underscore when SubtitleTrack has content. Way cleaner than fixing it later.
Got multiple optional fields? Group all the required fields first, then tack on the optional ones with separators built into the IF statements. Makes debugging complex naming schemes so much easier.
you could also use SUBSTITUTE after concatenating to clean up doubles. try SUBSTITUTE(your_concatenate_formula, "__", "_") if you don’t want to rewrite everything with IF statements. not as clean but it’s quick and works.
Had this exact problem when I was setting up automated naming for our media files. You need to wrap each field-separator combo in an IF statement that checks if the field has content before adding the underscore. Here’s what worked for me: CONCATENATE({Company}, "_", {ProjectType}, "_", {Resolution}, "_", {Season}, {Lang1}, {Lang2}, {Lang3}, {Lang4}, {Lang5}, IF({SubtitleTrack}, CONCATENATE("_", {SubtitleTrack}), ""), "_", {Build}, "_", {Format}, {FileExt}) When SubtitleTrack is empty, it returns an empty string instead of adding the underscore. You’ll probably need the same logic for other optional fields based on how your data’s structured. Bit more work upfront but gets rid of those annoying double separators.
Here’s what saved me tons of headaches: I built a custom function with REGEX_EXTRACT_ALL that handles separators dynamically. Instead of hardcoding underscores everywhere, I dump all my fields into an array first, filter out empty ones, then join with separators. Something like ARRAYJOIN(ARRAYCOMPACT([{Company}, {ProjectType}, {Resolution}, {Season}, CONCATENATE({Lang1}, {Lang2}, {Lang3}, {Lang4}, {Lang5}), {SubtitleTrack}, {Build}, {Format}]), "_") & {FileExt}. ARRAYCOMPACT strips out empty values before joining, so no more double separators. Takes a minute to click, but once it does, it’s perfect for variable-length filenames without messy nested IF statements.
I break this down into helper fields instead of cramming everything into one giant formula. I’ll make a ‘Languages’ field that combines Lang1-Lang5, and an ‘Optional’ field for SubtitleTrack stuff. Then my main formula just smashes the helper fields together without checking for empty values. Yeah, it uses more columns, but debugging is way easier when things go wrong. Also, if your base has REGEX_REPLACE, use it. You can clean up double underscores and separator mess in one shot instead of nesting IF statements all over the place.