Left Formula Acting Strange

[Lisa_Altizer]

I’m trying to use the LEFT formula to extract the first 8 characters of a linked record. All works well except for in Table 1 you see some of the records are including a single quote on the left side. It’s weird that not all the records are doing this.

Table 2 is where the linked record comes from it is also using a formula:

Airtable support gave me the following instructions and I’m having a difficult time understanding what they mean?
When referencing a linked record in a formula, a comma always triggers quotations around a linked record. To remove those, you can wrap the field in SUBSTITUTE() as in SUBSTITUTE({FIELDNAME},"\"","") to get rid of the quotes.

Any thoughts anyone?

Thanks in advance for helping out this not to formula savvy individual!

[Lisa_Altizer]

Oh goodness - God helped me understand what the support team was saying. Wrap SUBSTITUTE in the LEFT formula. I did this: LEFT(SUBSTITUTE({CRS Num/Title},"\"",""),8) and it worked!!!

Sorry to bother everyone.

[W_Vann_Hall]

For those reading over Lisa’s shoulder at a later date, the expression ‘\"’ in the formula from Airtable support is called an escaped double-quote; that is, it is a two-character expression that allows you to specify a literal double quotation mark character inside a text phrase enclosed within double quotation marks. (The backslash — ‘\’ — is an escape code that tells Airtable to treat the next character in the text as the literal representation of that character, even if it would otherwise have a special meaning within Airtable code. For instance, you couldn’t enclose a double quote within double quotes without the escape character — that is, as ‘"""’ — because the second double quote would close the expression opened by the first double quote, while the third double quote would open another, unclosed expression and trigger an error.)

Another, possibly less-confusing way to state that formula would be to use single quotes to enclose text expressions, as so:

SUBSTITUTE({FIELDNAME},'"','')

That also works in Airtable and — to me, at least — is more immediately obvious as to what are the target and substitution characters…