Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

Extract #3 item from a text list with separators

Solved
Jump to Solution
206 4
cancel
Showing results for 
Search instead for 
Did you mean: 

I have been playing around with @Justin_Barrett’s excellent script “Parent Child tree hierarchy” (Parent Child tree hierarchy).

The output of this is a text field with any separator you like, for example:
World > UK > London > Hammersmith > Elspeth Avenue > Joe’s Cafe
World > France > Paris > La Defence > BNP Paribas
World > USA > New York > Manhattan > Central Park

Question - is there any way to extract the #3 item (or the #2 or #4 for that matter) from these text strings? In this case, to pick out “London”, “Paris” or “New York”. I assume this should involve counting 2 instances of the separator (“>”), and then selecting the text from there up to the next separator.

1 Solution

Accepted Solutions

@Ed_Goble1 Glad that the script has been helpful! Here’s a REGEX solution to the problem:

IF(Origin, TRIM(REGEX_EXTRACT(Origin, "(?:[^>]*> ){2}([^>]*)")))

Screen Shot 2022-09-19 at 6.42.15 PM

The part that drives which piece is extracted is the number in the middle of the regular expression. That tells the interpreter how many pieces to skip before grabbing the next one in line. In other words, that number should be one smaller than the Nth piece that you actually want; e.g. if you want the 3rd piece, the number should be 2 (as in the above example). It will even work with zero to grab the first piece.

FWIW, if the delimiter—the character you’re using in-between each part—is something besides >, replace all instances of > with the appropriate delimiter. (NOTE: some characters have special meaning to a regular expression so this isn’t a blanket solution that will work with any delimiter, but it’ll likely work with most without further tweaking.)

See Solution in Thread

4 Replies 4

Hi @Ed_Goble1, if you’re trying to do this via formulas only, the only way I know how to do this is via a whole bunch of LEFT(), FIND() and SUBSTITUTE()s

So for example, #1 is easy, just do a

LEFT(
  {Text},
  FIND(
    ">",
    {Text}
  )
)

Once you hit #2, you have to do a SUBSTITUTE() to remove #1 from the original text, and then do the same thing:

LEFT(
  SUBSTITUTE(
    {Text},
    {#1} & " > ",
    ""
  ),
  FIND(
    ">",
    SUBSTITUTE(
      {Text},
      {#1} & " > ",
      ""
    )
  ) - 2
)

And so on and so forth
Screenshot 2022-09-19 at 6.32.46 PM

With luck someone else has a better idea heh

Thanks. This was great for my purpose!

(With the tiny addition of a “-2” towards the end of the first script so it didn’t return the " > ")

@Ed_Goble1 Glad that the script has been helpful! Here’s a REGEX solution to the problem:

IF(Origin, TRIM(REGEX_EXTRACT(Origin, "(?:[^>]*> ){2}([^>]*)")))

Screen Shot 2022-09-19 at 6.42.15 PM

The part that drives which piece is extracted is the number in the middle of the regular expression. That tells the interpreter how many pieces to skip before grabbing the next one in line. In other words, that number should be one smaller than the Nth piece that you actually want; e.g. if you want the 3rd piece, the number should be 2 (as in the above example). It will even work with zero to grab the first piece.

FWIW, if the delimiter—the character you’re using in-between each part—is something besides >, replace all instances of > with the appropriate delimiter. (NOTE: some characters have special meaning to a regular expression so this isn’t a blanket solution that will work with any delimiter, but it’ll likely work with most without further tweaking.)

Wow. Even better. I just added an IF statement to strip out the Error you get if there aren’t enough levels and it’s more elegant (in my case) to return a blank. I’m very impressed by this community. Thanks both!