EXTRACTNUMBERS() only numbers greater than 99999

I use EXTRACTNUMBERS() to get numbers from a OCRTEXT() column.
This produces a list with numbers like:

1000 , 50 , 5 , 216423 , 3 , 5 , 214517 , 214506 , 214505 , 214516 , 214501 , 214477 , 214515 , 1 , 214504 , 120728 , 217435 , 3 , 214512 , 214507 , 751 , 846 , 951 , 49 , 5 , 8 , 2019

I want to extract only the numbers that have 6 characters like:
AND(>99999 , <1000000)

How can I do that?

Hi Fabian, as far as I know, there is no workaround available to LOOP over the list type fields nor restructure the list items with conditions.

I know this is what you want but you may need to ask your user to select the numbers which are extracted by the extractnumber formula manually.
I mean you place another physical column with Enum list type. And suggested value to refer to your extractnumber fields. Then it will generate the dynamic list, and then user will pick up number they want… No dynamic way to select only the value/number matching the condition etc.

Thank you @tsuji_koichi this is what I’m doing right now.
I added a feature request :blush:

1 Like

Sure, thanks Fabian.

To solve your problem, which is actually my problems, I wish to raise another thread for the feature request. Will be back with that.

3 Likes

Solution:

See LoopingWithActions here:

https://www.appsheet.com/portfolio/381190

Do you need to preserve the order of the remaining values after they have been extracted?

If not, then just sort the list then convert it to TEXT(), and SPLIT() it by “99999”. Then use INDEX() to get the half you want and SPLIT() it again by commas.

1 Like

Copied for later study.

This should do it, assuming [ExtractedSortedNumberList] is sorted high to low.

SPLIT(
	INDEX(
		SPLIT(
    	IFS(NOT(IN(999999, [ExtractedSortedNumberList])), [ExtractedSortedNumberList]+LIST(999999)),
    "999999")
  , 1)
, ",") - LIST("")
2 Likes

Thank you @Steve. Could you please explain what your Action are doing?

Thank you @GreenFlux and @Bahbus.
I never thought about the idea of adding a number and SPLIT() the result at this number. But I think this is a good solution.
So for my case it is:

SPLIT(
	INDEX(
		SPLIT(
			SORT(
				LIST(1000 , 50 , 5 , 216423 , 3 , 5 , 214517 , 214506 , 214505 , 214516 , 214501 , 214477 , 214515 , 1 , 214504 , 120728 , 217435 , 3 , 214512 , 214507 , 751 , 846 , 951 , 49 , 5 , 8 , 2019)
				+LIST(99999)
			,TRUE)
		,"99999")
	,1)
,",")
2 Likes