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

AppSheet AppSheet Q&A
1

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?

2

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)
5 Likes
3

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

1 Like
4

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!

1 Like