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
    • 1 yr ago
    • Reported - view

    Is the text after the dash always four letters?

    Or

    Is there another pattern after the dash?

    • Fred
    • 1 yr 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
    • 1 yr 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
      • 1 yr 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
    • 1 yr 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
    • 1 yr 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
    • 1 yr 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
      • 1 yr ago
      • Reported - view

      what is your code?

    • bflack
    • 1 yr ago
    • Reported - view

      • bflack
      • 1 yr ago
      • Reported - view

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

      • Fred
      • 1 yr ago
      • Reported - view

      is the DB in the cloud or local/iCloud?

      • bflack
      • 1 yr ago
      • Reported - view

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

      • bflack
      • 1 yr ago
      • Reported - view

       using local/icloud version

      • Fred
      • 1 yr ago
      • Reported - view

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

      • bflack
      • 1 yr ago
      • Reported - view

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

      • bflack
      • 1 yr ago
      • Reported - view

       this screenshot shows more within the formula input field

      • Fred
      • 1 yr 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
      • 1 yr ago
      • Reported - view

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

      • Fred
      • 1 yr ago
      • Reported - view

      can you post a sample DB?

    • bflack
    • 1 yr 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
    • 1 yr ago
    • Reported - view

    try:

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

      • Rafael Sanchis
      • Rafael_Sanchis
      • 1 yr 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
      • 1 yr 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
      • 1 yr ago
      • Reported - view

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

      • Fred
      • 1 yr 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.