Skipping empty fields in a naming formula

Question

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.

Ben_Young1 · Accepted Answer

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:

ryland-dxdl · Answer

@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!

Sign up

Login with SSO

Login to the community

Login with SSO