How To Clean Up Special Characters

[Stelio_Pappas]

Hi all

I have an app that is reading a QR code that appears to be delimited by a newline "\n".

A typical QR code (which I don't have control over) looks like this:

AC123456\nJoe\nBloggs\n01 Jan 1970\nJane Bloggs\n0412 345 678\nMy Cycling Club\nRace - All Discipline - Concession - Annual\nActive\n10 Mar 2023

I'm having trouble with the last field but no such trouble with other fields, including another date field.

The fourth field is a date of birth and the tenth (last) field is the expiry date.

Weirdly, I'm able to work with the fourth field but have difficulty with the last, despite using the exact same functions to manipulate the field into DD/MM/YY. -Although I was confident that I had my date formatting set up correctly, I eliminated that as a possible issue, by aiming to strip it down to just the DD portion and listing it as a text field in both my spreadsheet (Format > number > plain text) and my appsheet column type as text.

If I split the last field as follows;

SPLIT(INDEX(SPLIT([QRCode],"\n"),10)," ")

.... I get "10,Mar,2023" as expected.

If I attempt to strip it down further with;

INDEX(SPLIT(INDEX(SPLIT([QRCode],"\n"),10)," "),1)

.....I get a "10" as expected in the spreadsheet, but if I look carefully I notice there is actually a single quote before it that is not being displayed in the cell (Q19).

For some reason, this doesn't happen to the fourth field.

Although it seems to be introduced when I use the INDEX function, I figured the backslash from the original data might be the root cause.

I tried passing the [QRCode] column through a SUBSTITUTE function using a different delimiter in virtual column called [QRCodeSanitised] in the hope I could work against that, but frustratingly the problem persists.

Does anyone know how I can strip out the pesky single quote, or how best to handle special characters to avoid this issue?

Thanks
Stel

[Steve · Accepted solution]

The single quote is telling the spreadsheet to interpret the cell value as text, rather than as the numeric value it appears to be. Try changing the column type of the column in the app to Number.

[Stelio_Pappas]

Thanks Steve, that makes sense and explains why I didn't see it until I used the INDEX function.

The weird thing is that both dates were working at one stage, but the app one day starting having issues with the second date so I started messing about to identify the problem and ended up going on a wild goose chase with the apostrophe. -I've just set up the second date as I had it before and now its working again.

[Steve]

This will produce a list of two Date values:

EXTRACTDATES(
  CONCATENATE(
    LIST(
      INDEX(SPLIT([QRCode], "\n"), 4),
      INDEX(SPLIT([QRCode], "\n"), 10)
    )
  )
)