How to pull last element from Lookup field array?

[samb1]

Hi all! 

I was wondering if it's possible to use a formula to pull the last added item in a list of values in a Lookup field? The items are non-numerical so MAX(values) doesn't work. 

For context, there is a table for Members and a table for Posts. Posts can be either "topics" or "replies" and are all listed in the same "Posts" table. There can be multiple replies for a single topic and the replies are linked to the topic as a related record. But it's not possible for multiple topics per reply.

The Lookup field is on the Posts table and pulls the member's rank from the Members Table (rank is essentially their role e.g. manager, expert etc), but that comes through as an array. I want to know the rank of the last member who replied so that I can use this value in a formula field (I have a separate formula that's applying a prioritisation level based on different factors and member rank is one of the factors).

As there can be any number of replies I'm not sure how to only extract the rank of the last member that replied. Does anyone know what formula I could use to always get the last item in an array of varying length?

Thanks in advance!

[ScottWorld]

This is very easy to accomplish — check out the video that I posted at the bottom of this thread.

[TheTimeSavingCo]

You could use good ol' regex for this:

And the formula used is:

REGEX_EXTRACT(
  ARRAYJOIN({Label (from Table 1)}),
  '[^,]*$'
)

This'll break if your values contain commas though, but given you're talking about ranks it probably doesn't?  Let me know if it does tho and I'll see if I can figure something out

Link to base

[Zapier_Test]

Thanks @ScottWorld! I tried that approach but it couldn't get it to work for my use case. But that might just have been me not doing it right!  

[Zapier_Test]

Thank you @TheTimeSavingCo! I tried using that Regex formula you shared and it worked perfectly! 

Problem solved! 😁 

[Andrew_Davies]

Nice!  Works in the formula field of a Rollup column too - just what I needed!  Noted it breaks if a , is entered as a value.