expression to extract a text value from a column with a list of values

[fnorrmann]

Hi, I have a field that I scan with a QR code, and the value it stores in the field is as follows:

P/N:S0-36732V-12XF; SN:MP05466570FE35E; IMEI:123456789; SW:283348483_OTA

What I would like to to is to create a virtual column in AppSheet called "IMEI" with an expression that extracts a specific part of this field to get the IMEI number, which in this example would be the one shown in red:  123456789

I assume some type of Regex expression will do the job?

[WillowMobileSys · Accepted solution]

Regex is not supported in AppSheet.  AppSheet does have EXTRACT... functions but I don't think any of those will work in this case.

I would use the SPLIT and INDEX functions.   The idea is to split on the text on "IMEI:".   If your value is always the same length of numbers then you can use the LEFT function to get that value.  Otherwise, split again on the ";" and get the first item with Index.

So...if the value is a known length of 9 digits then:

LEFT(INDEX(SPLIT([QR Result], "IMEI:"), 2), 9)

 If length is not know then:

INDEX(SPLIT(INDEX(SPLIT([QR Result], "IMEI:"), 2), ";"), 1)

[SkrOYC]

Quite clever, I like this.

Without changing the subjet of the OP, have you tried using INDEX() and SPLIT() to parse data from a PDF417? It should work almost the same basically, with a bunch of tags in this case

[fnorrmann]

Excellent solution, I used the variable length flavor. It beats having to do a Regexextract formula in sheets and read the column in as read only (which is a workaround if you want to do it in sheets).

Thank you!