Unwanted quotation marks in pulled through by formula

[Thomas_Lea]

I have a formula that pulls text from another field. That field is a link to another table. Airtable is creating quotation marks around the text if the text contains any punctuation marks. Is there a way of stopping this??

[Justin_Barrett]

@Jack_Albers I think you might have misread the formula that I wrote above. Your formula actually adds quotes, taking each existing quote and doubling it. Here’s what it should be:

SUBSTITUTE(CONCATENATE({Customer}, "-", {Project Street Address}, "-", {Project Number}), '"', '')

Here’s an alternate way to write it using the & concatenation operator instead of the CONCATENATE() function. Slightly shorter, same result, but a hair cleaner (“concatenate” is such a cumbersome word IMO):

SUBSTITUTE({Customer} & "-" & {Project Street Address} & "-" & {Project Number}, '"', '')

[Jack_Albers]

Justin_Barrett:

SUBSTITUTE({Customer} & "-" & {Project Street Address} & "-" & {Project Number}, '"', '')

Thank you so much Justin! @Justin_Barrett

That worked great, I was trying to use the same logic on another one that is similar but I am adding a text of “PO#” instead of a specific field and I cant seem to get rid of the quotations. Sorry to bother you but slowly learning the power of the formulas and if you had any good forums to teach these formulas I would be greatly appreciated.

SUBSTITUTE({PO#} & “-” & {autonumber} & “-” & {Project}, ‘"’, ‘’)

[Justin_Barrett]

@Jack_Albers The formula you wrote looks good, but it doesn’t look like it matches the screenshot that you posted. Your formula is inserting hyphens as separators between pieces, but I don’t see those hyphens anywhere in that {Name} field output. Perhaps you copied that formula from a different field?

Jack_Albers:

if you had any good forums to teach these formulas I would be greatly appreciated.

Funny you should ask. :winking_face: I’m working on a very detailed course all about Airtable formulas. It’s too early to talk about a release date, unfortunately, but my hope is that it will make learning Airtable’s formula system much easier for new users, and maybe help seasoned Airtable users as well.

[WilliamPorter]

I know this is an old thread, but I ran into this behavior again today and I decided I should figure out WHY Airtable does this. It’s easy enough to remedy. But is there a rationale for this behavior?

[kuovonne]

When you use a linked record field in a formula field, if the primary field value has a comma or quote, then Airtable adds the quotes to clarify where the value actually begins and ends. This behavior is very similar to how a csv file may have extra quotes surrounding text strings with internal commas.

If you want an easy way to get rid of these extra quotes, use the “Show linked record without extra quotes” formula in my app Ready Made Formulas. This formula is more robust than the formulas that merely strip the opening and closing quote, as Airtable actually doubles internal quotes, and that requires extra handling. None of the previous formulas in this thread will handle internal quotes correctly.

Here is a screen shot of the app. Note that this formula only works if there is only one linked record field. (If you had multiple linked records, you would probably want to keep the extra quotes anyway.)

[WilliamPorter]

Thanks, Kuovonne. I didn’t have any trouble writing a formula to remove the quotation marks.

Your explanation — that the quotation marks are designed to clarify what’s a field delimiter and what’s not — is interesting. I should have thought of that myself. Of course I’m familiar with csv files putting quotation marks around values with commas for just this reason.

But I’m not sure I find it a satisfactory defense of the fact that Airtable does it. Out of 1000 Airtable users who refer to linked values, how many actually WANT the quotation marks to be there? My guess is that the number is pretty low, so I think it would make sense for Airtable automatically to display the linked value as it was entered in the linked table, that is, without the quotation marks. The 0.02 percent of users who WANT the quotation marks can very easily ADD them.

And I don’t understand why this occurs only with linked tables. Say I have a Name field that contains the value

William Porter, Jr.

and in the same table I create a formula field with this formula:

Name

that is, it simply creates a non-editable duplicate of the name value. What that formula field shows is

William Porter, Jr.

not

"William Porter, Jr."

Although when it is exported, that value will of course be wrapped in quotation marks. So the idea that Airtable does this because it’s how it’s done in CSV files doesn’t seem compelling to me. Airtable does NOT behave this way consistently. And in any case, Airtable is NOT a CSV.

Just seems like a bug to me. Just a little one, but midges are bugs, too. :slightly_smiling_face:

William

[kuovonne]

WilliamPorterTech:

The 0.02 percent of users who WANT the quotation marks can very easily ADD them.

I don’t think that it would be that easy for users to realize that they should add them.

In any case, this is legacy behavior and is unlikely to change.

WilliamPorterTech:

And I don’t understand why this occurs only with linked tables.

It doesn’t do this only with linked records. It also does this with multiple select values. Airtable doesn’t do this with single line text fields because single line text fields cannot hold multiple values.

[WilliamPorter]

kuovonne:

It doesn’t do this only with linked records. It also does this with multiple select values. Airtable doesn’t do this with single line text fields because single line text fields cannot hold multiple values.

Ah, thanks for sticking with me here. That makes somewhat more sense of this behavior. The point isn’t to delimit a specific linked value from other fields in the table – but to delimit it from other values in the same field. Thanks Kuovonne.

[Elizabeth_Aucti]

I am having this quotation issue, I was hoping someone could help. I added the ready made formula app, but I have multiple linked fields so it’s not applicable.

Below is my formula:
IF({Auction Type}= ‘Online’, CONCATENATE(“ :movie_camera: ”,{Auction Type}, " ", “-”, " ", Properties, " ", “-”, " ", Agent), IF({Auction Type}= ‘Onsite’, CONCATENATE(“ :house_with_garden: ”,{Auction Type}, " ", “-”, " ", Properties, " ", “-”, " ", Agent), IF({Auction Type}= ‘In Room Event’, CONCATENATE(“🙋”,{Event Office}, " ", “-”, " ",{Date}, " ", “-”, " ", {Auction Type}), IF({Auction Type}= ‘Online Event’, CONCATENATE(“ :pushpin: ”,{Event Office}, " ", “-”, " ",{Date}, " ", “-”, " ", {Auction Type})))))

Screenshot here:

[Justin_Barrett]

Wrap the following around your existing formula:

SUBSTITUTE( YOUR_FORMULA_HERE , '"', "")

You can also greatly simplify your formula design in a few ways. One is to combine adjacent literal strings into a single string. For example, there are several places in your formula where you’re doing this, adding single characters as separate strings:

... , " ", "-", " ", ...

Each of those could be changed to this:

... , " - ", ...

In addition, the overall structure could be simplified using the SWITCH() function, and also by using the & concatenation operator instead of the CONCATENATE() function.

With all of those changes made, your formula looks like this:

SUBSTITUTE(
    SWITCH(
        {Auction Type},
        "Online", "🎥" & {Auction Type} & " - " & Properties & " - " & Agent,
        "Onsite", "🏡" & {Auction Type} & " - " & Properties & " - " & Agent,
        "In Room Event", "🙋" & {Event Office} & " - " & {Date} & " - " & {Auction Type},
        "Online Event", "📌" & {Event Office} & " - " & {Date} & " - " & {Auction Type}
    ), '"', ""
)