Skip to main content

I work in a digital archive and I'm using Concatenate to create a unique naming formula based on certain fields in a spreadsheet. However, some items have different naming requirements. What I would like to do is be able to tell this formula to skip over any empty fields and only pull the ones with data in them for the naming. I would additionally like a way to tie the dash "-" to the numbers so that only the correct number of dashes appear for however many fields there are in the name. Here is the formula I am currently using. 

CONCATENATE({Collection ID}, "-" , RIGHT("00"&{Box No.},2), "-", RIGHT("00"&{Folder No.},2), "-" , RIGHT("0000"&{Item No.},4))

This will produce a name like "collectionID-box-folder-item". Ideally I would like it to be able to produce names like "collectionID-box-item" or "collectionID-folder-item".

Apologies if this is a bit unclear, I am not very familiar with this kind of work. 

Hey @ryland-dxdl! 

Here are two formulas that accomplish the same thing in two different ways.
Since these formulas are rather bulky, if you try these formulas and they throw you an error, I would run through and doublecheck that all your field names are correctly formatted.

TRIM(
IF(
{Collection ID},
{Collection ID} &
IF(
{Box No.},
"-",
IF(
OR({Folder No.}, {Item No.}),
"-"
)
)
)
&
IF(
{Box No.},
RIGHT("00" & {Box No.}, 2) &
IF(
{Folder No.},
"-",
IF(
{Item No.},
"-"
)
)
)
&
IF(
{Folder No.},
RIGHT("00" & {Folder No.}, 2) &
IF(
{Item No.},
"-"
)
)
&
IF(
{Item No.},
RIGHT("0000" & {Item No.}, 4)
)
)

Here's another approach:

IF(
OR({Collection ID}, {Box No.}, {Folder No.}, {Item No.}),
IF(
REGEX_MATCH(
TRIM(
IF(
{Collection ID},
{Collection ID} & "-"
)
&
IF(
{Box No.},
RIGHT("00" & {Box No.}, 2) & "-"
)
&
IF(
{Folder No.},
RIGHT("00" & {Folder No.}, 2) & "-"
)
&
IF(
{Item No.},
RIGHT("0000" & {Item No.}, 4)
)
),
"(^-|-$)"
),
REGEX_REPLACE(
TRIM(
IF(
{Collection ID},
{Collection ID} & "-"
)
&
IF(
{Box No.},
RIGHT("00" & {Box No.}, 2) & "-"
)
&
IF(
{Folder No.},
RIGHT("00" & {Folder No.}, 2) & "-"
)
&
IF(
{Item No.},
RIGHT("0000" & {Item No.}, 4)
)
),
"(^-|-$)", ""
),
TRIM(
IF(
{Collection ID},
{Collection ID} & "-"
)
&
IF(
{Box No.},
RIGHT("00" & {Box No.}, 2) & "-"
)
&
IF(
{Folder No.},
RIGHT("00" & {Folder No.}, 2) & "-"
)
&
IF(
{Item No.},
RIGHT("0000" & {Item No.}, 4)
)
)
)
)

 Here's the resulting behavior:

 

@Ben_Young1 Thank you so much! I was able to plug this in and it worked instantly. I am adding more fields to the spreadsheet and formula and everything is still working exactly like I wanted it to. Thanks so much for your assistance! 

Reply


Terms & Conditions