EXTRACTING NUMBER - am I doing something wrong?

hunaism · 05-14-2020 11:55 AM

Ok, so I have an OCR function implemented and OCR_Text column (LongText). Sample data like this:

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:

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

Steve

Try:

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

See also:

hunaism

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

Steve

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

hunaism

thanks @Steve
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" ,""),
		"{" ,""),
		"|" ,""),
		"}" ,""),
		"~" ,"")
)

111155

Hello. please tell me in which field you need to put the formula so that it works

Steve

As the Initial value expression of whatever column is to contain the resulting value. Note that this column may not be the same as the column that contains the original scanned or input value.

111155

Hello. please tell me in which field you need to put the formula so that it works