Help With Formula to Join Text In Fields

[Tom_Blanchard]

Hi All - Hoping someone can help me figure out a formula to create the result outlined in the attached image. Involves stringing together text from fields, as well as replacing spaces with underscores, and shortening some Names to be Initials Only, etc.

If possible to create a Loom or Video explaining how to do this, I would be so grateful! I can’t seem to figure out how to properly put all the proper formulas together to make this happen but know it can be done. Thanks!

[Databaser]

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?

[Tom_Blanchard]

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!

[Tom_Blanchard]

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))

[Justin_Barrett]

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 #} & "_####"