EXTRACTING NUMBER - am I doing something wrong?

Ok, so I have an OCR function implemented and OCR_Text column (LongText). Sample data like this:
Adnotacja 2020-05-14 194617
I would like to extract digits out of the OCR_Text and store it as Number so I’m doing following tests with virtual columns:
Column “temp3” =EXTRACT(“NUMBERS”, [OCR_Text])
Column “temp4” =EXTRACTNUMBERS([OCR_Text])
the results are as follows:
Adnotacja 2020-05-14 205201

The funny thing I noticed that EXTRACT(“NUMBERS”, TRIM(SUBSTITUTE([OCR_Text], " ", “”))) returns no List/Value

Try:

NUMBER(
  SUBSTITUTE(
    INDEX(
      SPLIT(
        [OCR_Text],
        "ODO "
      ),
      2
    ),
    " ",
    ""
  )
)

See also:



1 Like

Thanks @Steve it works for this particular photo, but in case there would be no "ODO " recognized in next OCR it would not - I think the best approach would be to replace all possible and not needed characters from the OCR Text and than recognize the number.
I will make some tests with SUBSTITUTE function you’ve shared - thanks.

BTW, what if I could create a LIST() with all not needed characters and make the replacement at once - is it possible?

Anyways… below code seems to work:

NUMBER(
	SUBSTITUTE(
	SUBSTITUTE(
	SUBSTITUTE(
	SUBSTITUTE(
	SUBSTITUTE(
	SUBSTITUTE(
	SUBSTITUTE(
		SUBSTITUTE([OCR_Text], 
			" ",""),
			"M",""),
			"k", ""),
			"m", ""),
			"/", ""),
			"h", ""),
			"O", ""),
			"D", "")
)
2 Likes

Unfortunately, AppSheet has no accommodation for that approach. Nested SUBSTITUTE() expressions is the best you can do. :frowning:

thanks @Steve :ok_hand:
In case someone is lookin for quick solution: copy-paste below to have just numbers from OCR_Text field

NUMBER(
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(
	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(
	SUBSTITUTE(
	SUBSTITUTE(
	SUBSTITUTE(
	SUBSTITUTE(
	SUBSTITUTE(
	SUBSTITUTE(
	SUBSTITUTE(
	SUBSTITUTE(
	SUBSTITUTE(
	SUBSTITUTE(
	SUBSTITUTE(
	SUBSTITUTE(
	SUBSTITUTE(
	SUBSTITUTE(
	SUBSTITUTE(
	SUBSTITUTE(
	SUBSTITUTE(
	SUBSTITUTE(
	SUBSTITUTE([OCR_Text], 
		" ",""),
		"!",""),
		"#",""),
		"$",""),
		"%",""),
		"&",""),
		"-",""),
		"(",""),
		")",""),
		"*",""),
		"+",""),
		"/",""),
		":",""),
		";",""),
		"<",""),
		"=",""),
		">",""),
		"?",""),
		"@",""),

		"A" ,""),
		"B" ,""),
		"C" ,""),
		"D" ,""),
		"E" ,""),
		"F" ,""),
		"G" ,""),
		"H" ,""),
		"I" ,""),
		"J" ,""),
		"K" ,""),
		"L" ,""),
		"M" ,""),
		"N" ,""),
		"O" ,""),
		"P" ,""),
		"Q" ,""),
		"R" ,""),
		"S" ,""),
		"T" ,""),
		"U" ,""),
		"V" ,""),
		"W" ,""),
		"X" ,""),
		"Y" ,""),
		"Z" ,""),
		"[" ,""),
		"\" ,""),
		"]" ,""),
		"^" ,""),
		"_" ,""),
		"`" ,""),
		"a" ,""),
		"b" ,""),
		"c" ,""),
		"d" ,""),
		"e" ,""),
		"f" ,""),
		"g" ,""),
		"h" ,""),
		"i" ,""),
		"j" ,""),
		"k" ,""),
		"l" ,""),
		"m" ,""),
		"n" ,""),
		"o" ,""),
		"p" ,""),
		"q" ,""),
		"r" ,""),
		"s" ,""),
		"t" ,""),
		"u" ,""),
		"v" ,""),
		"w" ,""),
		"x" ,""),
		"y" ,""),
		"z" ,""),
		"{" ,""),
		"|" ,""),
		"}" ,""),
		"~" ,"")
)
1 Like