I’m building a formula in Airtable to create file names by combining multiple field values. The issue is that some records don’t have data in all fields, which creates unwanted double underscores in the final result.
When SubtitleField is empty, I get something like “Company_Category_Size_Series__Release_Format.ext” with those double underscores. How can I modify this to skip the separator when a field is blank?
wrap each field in an if statement like if({SubtitleField}, concatenate("_", {SubtitleField}), "") - it’ll only add the underscore when there’s actual data. tedious but works great for avoiding double underscores.
You could also use TRIM with REGEX_REPLACE for cleaner separator handling. Try REGEX_REPLACE(TRIM(CONCATENATE(...)), "_+", "_") - it’ll collapse multiple underscores into just one. Super helpful when you’ve got variable empty fields since it catches those edge cases where you end up with three or four underscores bunched together. TRIM also cleans up trailing separators when your last fields are empty. I’ve been using this for client file naming and it handles messy data really well without rebuilding the whole formula.
SUBSTITUTE works great for fixing those double underscores. Just wrap your whole formula like this: SUBSTITUTE(your_concatenate_formula, "__", "_") and it’ll clean up any doubles automatically. Way easier than adding IF statements everywhere, and it handles multiple empty fields in a row without breaking. I’ve done this on tons of database projects - much cleaner than conditional logic all over the place, especially with optional fields like your language ones.
ARRAYCOMPACT strips out empty values before joining, so no double separators. Perfect when you’ve got optional fields like those language ones.
I’ve used this in data pipelines with messy input data. The compact function handles edge cases without writing conditional logic for every field. Just remember to include the dot in your Extension field since this treats it as part of Format.