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
    • Fred
    • 8 mths ago
    • Reported - view

    Is the text after the dash always four letters?

    Or

    Is there another pattern after the dash?

    • Fred
    • 8 mths ago
    • Reported - view

    Here is a quick and dirty regex code that searches for both double quotes and curly double quotes, but I don't know how to put double quotes into extractx() since Ninox thinks they are Ninox code.

    Once someone smarter can tell us how to escape double quotes, then you can put both results into a JSON like shown in this response.

    Then you can filter the JSON to get the correct response.

    • bflack
    • 8 mths ago
    • Reported - view

    Unfortunately the text afterward varies, sometime multiple words. But since I'm only using this for sorting alphabetically, maybe using the first four letters would work.

      • bflack
      • 8 mths ago
      • Reported - view

       Another thought, the number of characters AFTER the dash seems consistent, so maybe the script could include a range of characters after the dash ... Pavé Nameplate Necklace - "ADHD": that is extract between 2 and 6 characters after the dash. I think that would be sufficient to sort.

    • Fred
    • 8 mths ago
    • Reported - view
     said:
    Unfortunately the text afterward varies, sometime multiple words.

     Do the multiple words exist outside the quotes?

    Here is something that will work easily in extractx() but will give you the quotes.

    (?<=- )(.*)
    

    This will give you everything after the dash and space.

    So something like:

    extractx(fieldName, "(?<=- )(.*)", "$1")
    

    But you still have quotes to deal with.

    • bflack
    • 8 mths ago
    • Reported - view

    I'll give it a shot. Unclear whether or not if including the quotes (what ever version) will change the sorting.

    • bflack
    • 8 mths ago
    • Reported - view

    I get a syntax error message: #ERR: SyntaxError: Invalid regular expression: invalid group specifier name. Sorry, regex code is beyond me, so I need help debugging. 

      • Fred
      • 8 mths ago
      • Reported - view

      what is your code?

    • bflack
    • 8 mths ago
    • Reported - view

      • bflack
      • 8 mths ago
      • Reported - view

       a screen shot in a formula field. Item is the field with the product labels.

      • Fred
      • 8 mths ago
      • Reported - view

      is the DB in the cloud or local/iCloud?

      • bflack
      • 8 mths ago
      • Reported - view

      the quoted name ("AHDH" - which varies) is always at the end of the text  string

      • bflack
      • 8 mths ago
      • Reported - view

       using local/icloud version

      • Fred
      • 8 mths ago
      • Reported - view

      where is the error message? I don't see a red box around the 1.

      • bflack
      • 8 mths ago
      • Reported - view

       no red box, this screenshot is from the table view, not within the formula.

      • bflack
      • 8 mths ago
      • Reported - view

       this screenshot shows more within the formula input field

      • Fred
      • 8 mths ago
      • Reported - view

      weird, I don't get that. Not sure where to start.

      What version of the app are you using? I'm on 3.11.5

      You can try adding:

      extractx(fieldName, "(?<=- )(.*)", "", "$1")
      
      • bflack
      • 8 mths ago
      • Reported - view

      Well, thanks. Maybe someone else will have insight. I'm beyond stumped.

      • Fred
      • 8 mths ago
      • Reported - view

      can you post a sample DB?

    • bflack
    • 8 mths ago
    • Reported - view

    "Do the multiple words exist outside the quotes?" --didn't respond to your question ... yes there are multiple words before the dash, actually 3 with spaces in between. It would be fairly easy to make all the same, if that helps.

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

    try:

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

      • Rafael Sanchis
      • Rafael_Sanchis
      • 8 mths ago
      • Reported - view

       

      Excelent I clean a table with 8568 register with 1545 register with " 👍

      The 'Text 2' ? For what ?

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

       oops, the text 2 was my test yourtextfield and i forgot to rename it here in this example. So it should be replace(substr(YourTextField, index(YourTextField, "-") + 1), """", "")

      • bflack
      • 8 mths ago
      • Reported - view

       Yes, that's what I though. Any thoughts on dealing with smart quotes on some labels?

      • Fred
      • 8 mths ago
      • Reported - view

      It is funny that the Ninox docs don't even show replace() anymore. I wonder if they will eventually get rid of it.

Content aside

  • Status Answered
  • 8 mths agoLast active
  • 33Replies
  • 399Views
  • 5 Following