Help

Re: Select latest string separated by comma

2570 0
cancel
Showing results for 
Search instead for 
Did you mean: 
DanielAssayag
6 - Interface Innovator
6 - Interface Innovator

I have a string of Colors
Red, Blue, Yellow

I want to retrieve only the last value in the cell which is here Yellow.

The colors can change order so i can’t retrieve it by trimming a certain number of characters.

Thank you

6 Replies 6

I’m assuming your source string is from a rollup or lookup field. If so, the easiest method would be to create a formula field that right-pads each string to a common length and look- or rollup that field so you can use TRIM(RIGHT({Source},##).

If this is not from a rollup or lookup field, or if for some reason you can’t pad to a standard length, your only option is to use nested FIND()s to step through the string and extract the last value. Since Airtable currently does not support any sort of procedural looping, you’ll have to define an explicit extraction routine for as many values as you expect the maximum number will ever be. As you can see from this earlier post, this can be quite a painful exercise when dealing with strings containing 10 or more values.

I don’t understand why i would trim my Source with a fixed length when my Source entries have different lenghts.
I’m using lookups to retrieve multiples values linked to a single record. They then appear as separated by comma but can be interpreted as a single string.
I had to break it into a String:

Yellow, Blue, Red

Becomes

YellowBlueRed

I also retrieved the length in Len field of the Source (Colors) string and use the following command :
So for Colors: Yellow, Blue, Red and Lengths: 6, 4, 3 (or YellowBlueRed, 643)
RIGHT(ARRAYJOIN(Colors, ‘’), RIGHT(CONCATENATE(Len), 1))

=> Result in RIGHT(YellowBlueRed, 3)
gives Red

The problem for this method is that if the word you wrote is more than 9 caracters then would need to use RIGHT command with 2 characters but it will bug your results.

Your trick with rolling up the lengths of the various strings and then extracting the length of the last entry is brilliant — I’m going to steal that idea!

The reason I suggested you pad the length of your fields was so you could easily extract the last entry from a string with an unknown number of entries — but your trick with storing the lengths makes it unnecessary.

To get around the problem with entries over 9 characters long, instead of LEN({String}) as your formula for extracting and storing the length of each entry, try this:

IF(
    LEN(
        {EnteredString}
        )<10,
    '0'
    )&LEN(
        {EnteredString}
        )

That will store your entry lengths as a string from ‘00’ to ‘99’. Then, modify your extraction routine to read:

RIGHT(
    ARRAYJOIN(
        {Colors},
        ''
        ),
    VALUE(
        RIGHT(
            CONCATENATE(
                {Len}
                ),
            2
            )
        )
    )

That’s great!
Thanks for your help and for completing it!

:slightly_smiling_face:

I wonder now how i could do this with another type than Strings, for example retrieve the last Attachment in array. As when i flatten an array of Attachments i get smtg like:

inventions3.jpg (https://dl.airtable.com/.attachments/7b547150fb260d1254ca24ff620be8a0/1e17ea94/inventions3.jpg)

but then i cannot transform this back into an attachment associated with a thumbnails.

Anyway, just extra questions… for now the rest works :slightly_smiling_face:

Hi! I am trying this formula as well but it shows error. What I want is to retrieve the last couple of words (spaces between them) after the last comma. Example: go to the beach, go to the dessert, go to the space – I want a formula that gives: go to space.

Thanks so much for the formular! Awesome!