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?

0 10 461
10 REPLIES 10

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

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.

Solution:

Steve
Participant V

See LoopingWithActions here:

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

Copied for later study.

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

GreenFlux
Participant V

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.

Bahbus
Participant V

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("")

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)
,",")
Top Labels in this Space