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
-
Is the text after the dash always four letters?
Or
Is there another pattern after the dash?
-
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.
-
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.
-
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.
-
I'll give it a shot. Unclear whether or not if including the quotes (what ever version) will change the sorting.
-
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.
-
-
a screen shot in a formula field. Item is the field with the product labels.
-
is the DB in the cloud or local/iCloud?
-
the quoted name ("AHDH" - which varies) is always at the end of the text string
-
using local/icloud version
-
where is the error message? I don't see a red box around the 1.
-
no red box, this screenshot is from the table view, not within the formula.
-
this screenshot shows more within the formula input field
-
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") -
Well, thanks. Maybe someone else will have insight. I'm beyond stumped.
-
can you post a sample DB?
-
-
"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.
-
try:
replace(substr(YourTextField, index('Text 2', "-") + 1), """", "")
-
Excelent I clean a table with 8568 register with 1545 register with "
The 'Text 2' ? For what ?
-
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), """", "")
-
Yes, that's what I though. Any thoughts on dealing with smart quotes on some labels?
-
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
- 9 mths agoLast active
- 33Replies
- 415Views
-
5
Following