EXTRACTNUMBERS without spaces

[Christoph_Bucke]

Hi,
I have a question about the function ExtractNumbers. The function extracts the numbers found in an OCR text and returns a list.

Do you think the following behavior is intended?
extractnumbers(“41”) : (41) ok
extractnumbers(“41B”) : (4100000000) nok, should in my view be 41
extractnumbers("B464/116) : (116) nok, should in my view be a list (464 , 116)

Do you have an idea how I could get the wanted behavior?

I tried also to substitute each character but digits by one space. Interestingly the last example then returned 464.116
It was ok, when I substituted each character with 2 spaces.

Thanks for any advice

[MultiTech]

Hey @Christoph_Bucker

Since ExtractNumbers() is focused on numbers, it’s going to always struggle with handling any other characters.

In order to get this to work, you might have to first clear out all the letters and things - this way the ExtractNumbers() is given a clean input.

Clearing out all the letters is not a “pretty” looking process; it requires nesting a series of SUBSTITUE() statements, one for each letter/character you with to replace/remove.

Here's an example of a formula that I've been testing out for converting the text of a column into a sensical ID (to use as the Key value).

---

substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(

TRIM([ENTER_YOUR_COLUMN_HERE]),

" , ", “"),
", ", "”),
" “, “"),
“-”, "”),
left(”’ “, 1), “”),
left(’” ', 1), “”),
“/”, “”),
“.”, “_”),
“,”, “”),
“”, “”),
“:”, “”),
“;”, “”),
“[”, “”),
“]”, “”),
“(”, “”),
“)”, “”),
“{”, “”),
“}”, “”),
“!”, “”),
“@”, “”),
“#”, “”),
“$”, “”),
“%”, “”),
“^”, “”),
“&”, “”),
“*”, “”),
“+”, “”),
“?”, “”),
“|”, “”),
“<”, “”),
“>”, “”)

My general rule of thumb for keys is “Text/Hidden/UNIQUEID()” ALWAYS! But sometimes a sensical ID can help debugging things later.

---

If you examine the formula above, you can see that there is a dedicated SUBSTITUTE() for each character (or series of characters) that I want to remove/replace.

So you would start with something like the following:

SUBSTITUTE([OCR Result], “A”, “”)

Then wrap another around this for B:

SUBSTITUTE(SUBSTITUTE([OCR Result], “A”, “”), “B”, “”)

Etc.

It’s not pretty, but it gets the job done. Might be the only way to get ExtractNumbers() to play nicely.

[Steve]

Or don’t use EXTRACTNUMBERS().

[Christoph_Bucke]

Do you have another proposal how to identify the numbers?

[MultiTech]

Perhaps you could train the OCR model to ignore the letters?

[Christoph_Bucke]

Thanks for the proposal. I tried this and it works in principal. To be honest I do not like the solution, but it is working.

[GreenFlux · Accepted solution]

MultiTech_Visions:

SUBSTITUTE(SUBSTITUTE([OCR Result], “A”, “”), “B”, “”)

Christoph_Bucker:

Thanks for the proposal. I tried this and it works in principal. To be honest I do not like the solution, but it is working.

Yeah, it sucks having to nest 26 SUBSTITUTE()s just to filter out letters, and then there are still special characters to consider. But I think that’s the best we can do with the current functions in AppSheet.

We could REALLY use REGEX, or at least a more powerful SUBSTITUTE() with optional sets of parameters, or something like FileMaker’s FILTER().

Allow multiple sets of parameters in SUBSTITUTE, like SWITCH(), to avoid nesting multiple times Feature Requests

The SUBSTITUTE() function takes 3 arguments: SUBSTITUTE( template , old-text , new-text ) And when you want to substitute multiple values in the template, you have to nest multiple SUBSTITUTE()'s together: SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST…

REGEX() expressions and an optional occurrence parameter for SUBSTITUTE() Feature Requests

@praveen @tony Is it possible to have powerful expressions like: REGEXMATCH() (returns a TRUE/FALSE) REGEXEXTRACT() (returns TEXT) REGEXREPLACE() (returns TEXT) I’m aware that the EXTRACT() expression can perform alike but at the end this function returns a list and it’s a bit limited in nature. Additionally we need an optional occurrence parameter for the SUBSTITUTE expression which can save us from creating nested SUBSTITUTE expressions where expressions can become cleaner. SUBSTITUTE( t…

Another option is saving the raw value to the sheet, and then using a Google Sheet formula with REGEX to extract the numbers to a new sheet column.

Text type should not allow the user to type any number or Special Characters Questions

One other option is using a spreadsheet formula with REGEXMATCH. However, the formula result will not be applied until sync, so the user will not see that they are entering an invalid value. You would have to allow the invalid value, then trigger a delete or notification to the user if the validation failed. [Screen Shot 2020-07-15 at 8.02.59 AM]

[Christoph_Bucke]

Thanks for detailed explanation and the hint for the featue requests. I like the REGEX capabilities as well. Voted!