Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Mar 31, 2023 11:19 AM
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.
Solved! Go to Solution.
Mar 31, 2023 01:16 PM
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:
Mar 31, 2023 01:16 PM
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:
Apr 03, 2023 07:19 AM
@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!