Help

Re: Skipping empty fields in a naming formula

Solved
Jump to Solution
2364 0
cancel
Showing results for 
Search instead for 
Did you mean: 
ryland-dxdl
4 - Data Explorer
4 - Data Explorer

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. 

1 Solution

Accepted Solutions
Ben_Young1
11 - Venus
11 - Venus

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:

Snag_ead55e.png

See Solution in Thread

2 Replies 2
Ben_Young1
11 - Venus
11 - Venus

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:

Snag_ead55e.png

ryland-dxdl
4 - Data Explorer
4 - Data Explorer

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