Help With Formula to Join Text In Fields

Hi @Tom_Blanchard

Looking at all the requirements, I’m guessing you’ll need some REGEX-magic for this one… Unfortunately, I’m no expert. Maybe @Justin_Barrett can point you in the right direction?

Hi @Databaser, I was able to figure it out, but I had to create a few separate formulas to do so. I couldn’t figure out how to put all these formulas into 1 field. Ideally that’s what I’d like to do. @Justin_Barrett , any help would be super appreciated!

Here’s the formulas:

REGEX_REPLACE(
CONCATENATE({Project Code},"_",{Project Name},"_",{File Name Helper: Photo Initials},"_",{File Name Helper: Athlete 1st Initial},{File Name Helper: Athlete Last Name},"_","Look-",{Look #},"_####"),
" ","_")

REGEX_REPLACE({Photographer},'[a-z]+[a-z\\s]','')

LEFT({Athlete/Catalyst},1)

RIGHT({Athlete/Catalyst},LEN({Athlete/Catalyst})-SEARCH(" ",{Athlete/Catalyst},1))

While it’s possible to make a mega-formula that has everything (see below), the divide-and-conquer approach that you came up with has its benefits, @Tom_Blanchard . To take that a step further, consider adding specific formulas to the tables where the data resides. For example, add the formula that creates the photographer’s initials to the table containing the photographer records. Then you can look up the abbreviation and use that in the assembly formula.

As for the formulas themselves, you came up with some good options. Here are variations to consider for some of them.

For the photographer’s initials (slightly simpler REGEX, plus it will account for hyphenated names; e.g. James Allen-Smith would become JAS):

IF({Photographer}, REGEX_REPLACE({Photographer}, "[a-z\\s-]", ""))

For the first-initial-last-name combo, a single formula can do the job. (Side note: I discovered purely by accident that if you leave out the index for the LEFT() function, it defaults to 1):

IF({Athlete/Catalyst}, LEFT({Athlete/Catalyst}) & REGEX_EXTRACT({Athlete/Catalyst}, "(?:.* )(.*)"))

As a side note, the only part that really needs the space-to-underscore replacement is the project name. While there’s no harm wrapping the REGEX_REPLACE() function around the whole shebang, I prefer to focus function use where it’s actually needed.

If you want to have an all-in-one formula, here you go:

{Project Code} & "_" &
REGEX_REPLACE({Project Name}), " ", "_") & "_" &
REGEX_REPLACE({Photographer}, "[a-z\\s-]", "") & "_" &
LEFT({Athlete/Catalyst}) & REGEX_EXTRACT({Athlete/Catalyst}, "(?:.* )(.*)") & "_" &
"Look-" & {Look #} & "_####"

If you follow my tip above and move some formulas into their relevant tables and use lookup fields to pull the result into your main table, this could be simplified further (assuming that all of the “Formatted” fields below are lookup fields for the appropriate linked records):

{Project Code} & "_" &
{Project Name Formatted} & "_" &
{Photographer Formatted} & "_" &
{Athlete/Catalyst Formatted} & "_" &
"Look-" & {Look #} & "_####"