0

Expression to extract text in a string to the right after a dash character

I need to extract text in strings (product labels) after a dash character. An example string: Pavé Nameplate Necklace - "ADHD". Ideally, the result would be ADHD, but "ADHD" works as well, since I will use the extracted text to sort alphabetically. 

I'm avoiding extracting text between quote marks since many of the label-strings have inconsistent straight-quote marks and smart-quotes marks --but maybe an expression can account for the different quote mark styles.

Thanks in advance!

Bob

33 replies

null
    • Ninox partner
    • RoSoft_Steven.1
    • 7 mths ago
    • Reported - view

    try:

    replace(substr(YourTextField, index('Text 2', "-") + 1), """", "")

    • bflack
    • 7 mths ago
    • Reported - view

    This works great when the labels have straight-quotes, but sometimes there are smart-quotes (curled marks). Is there a way to cover for both to avoid editing hundreds of labels?

      • bflack
      • 7 mths ago
      • Reported - view

       Here's a screen shot of the script applied to Trying or TRYING labels. The top one had straight quotes and came out right. The next two had leading curly quotes and ending straight quotes. I assume I'm correctly identifying the leading quotes as "curly". Please correct me if I'm wrong.

      • Ninox partner
      • RoSoft_Steven.1
      • 7 mths ago
      • Reported - view

       

      with an extra replace i guess:

      let txt := replace(substr(YourTextField, index(YourTextField, "-") + 1), """", "");
      txt := replace(txt,""","");
      txt

      where the middle one " of the """ is a curly quote. I can't type here one on my ipad right now....

    • bflack
    • 7 mths ago
    • Reported - view

    This is sooo close. Definitely solved the left curly quote, but there are also a few right curly quotes. 

      • bflack
      • 7 mths ago
      • Reported - view

       Nailed it! See below ...

      let txt := replace(substr(Item, index(Item, "-") + 1), """", "");
      txt := replace(txt, "“", "");
      txt := replace(txt, "”", "");
      txt

      Many thanks !!

      Bob

    • Sean
    • 7 mths ago
    • Reported - view

    This one-liner works with the example given.

    extractx(YourTextField, "(?:""|“)(\w+)(?:""|”)", "$1")
Login to reply

Content aside

  • Status Answered
  • 7 mths agoLast active
  • 33Replies
  • 378Views
  • 5 Following