As you’ve found, AppSheet cannot easily do what you want. Support for VCARD is a good idea, though. I encourage you to submit a feature request!
AppSheet is in no way designed to easily carve up arbitrary text, so the only way you can approach this is with tricks. It’s not clear whether you want everything in the VCARD pulled out and recorded, or whether you want to be able to extract specific lines.
Suppose you want the N (name) line:
MID(
ANY(
SORT(
SPLIT(
SUBSTITUTE(
SPLIT(
[QR Code],
"
"
),
" , N:",
" , 000"
),
" , "
)
+ LIST("999(no name)")
)
),
4,
999999
)
The VCARD data is multiple lines, so we have to work with newlines, which makes indenting the expression for clarity difficult. There is one line above that consists solely of a double-quote (") at the very beginning of the line. That line must be exactly that or the expression will fail.
The idea is to mark the line we want with a prefix that sorts alphanumerically before everything else. Then, we convert the VCARD data to a list, sort it, and hopefully find our line at the top. It’s possible, though, the line we want is not included, so we also want to add a line with a default value that sorts after the line we want but before everything else. If the line we want isn’t there, our default value will be the top line.
-
SPLIT([QR Code], ...) splits the multi-line original into a list, one line per list element. The list is split around the newlines in the original, which is what makes this expression particularly delicate. This gets rid of the newlines.
-
SUBSTITUTE(..., " , N:", " , 000") locates the list element corresponding to the name (N) label. List elements are separated buy the character sequence, space-comma-space (,). The label (N) follows immediately, terminated by a colon (:). We replace that entire sequence with space-comma-space (,)–to preserve the list element–and the digits, 000. In alphanumeric sorting, numbers sort before letters, so when the list is sorted, this numeric prefix will occur before any labels (which presumably all start with a letter).
-
SPLIT(..., " , ") takes the textual output produced by SUBSTITUTE() and splits it back into a list.
-
... + LIST("999(no name)" adds the default value to the list. 999 sorts after 000 but before any label that starts with a letter. (no name) is the default value. You can make the default value whatever you want, or put nothing after 999 for a blank default value.
-
SORT(...) sorts the resulting list in alphanumeric order, low-to-high, numbers before letters.
-
ANY(...) pulls out the first item of the sorted list.
-
MID(..., 4, 999999) starts at the fourth character of the first item of the sorted list and grabs the first 999,999 characters, or up to however many there are. 4 corresponds to one more than the length of the numeric prefixes we used (000 & 999). 999,999 is just a large number that should cover every likelihood.
See also:
LinkedIn
Twitter
