EXTRACTING NUMBER - am I doing something wrong?

AppSheet AppSheet Q&A
,
1

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

2X_5_5acfb8a200e7fda41d4201a144597df9d6ce009a.jpeg
2X_5_5acfb8a200e7fda41d4201a144597df9d6ce009a.jpeg345×500 38.6 KB

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:

2X_2_25bc1b536b4709e5a4242f8556777eba13bf1110.png
2X_2_25bc1b536b4709e5a4242f8556777eba13bf1110.png341×186 886 Bytes

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

1 Like
2

Try:

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

See also:

1 Like
3

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
4

hunaism:

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

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

5

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" ,""),
		"{" ,""),
		"|" ,""),
		"}" ,""),
		"~" ,"")
)
6 Likes
6

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

7

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

8

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.

1 Like