Help

Re: Unwanted quotation marks in pulled through by formula

4861 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Thomas_Lea
4 - Data Explorer
4 - Data Explorer

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??

21 Replies 21

What is your formula? Or what are you trying to do?

That’s Airtable’s default behavior when accessing a text field with embedded punctuation. I know of no way to prevent it from happening — but you could always wrap your formula with something like

MID({Text},2,LEN({Text})-2)

@W_Vann_Hall
I may need your help on this on. Being from the Netherlands I keep running into issues related to quotation marks.

Your formula works for me, but… it works for all records (duh…). The thing is that unlike @Thomas_Lea only the records that have a comma included appear with the quotation marks. So, I figured I use a formula like
IF(LEFT({text},1)=""",MID({text},2,LEN({text})-2),{text})
The formula is not accepted because of the 3 quotation marks.

Do you have an idea how I can solve this?
Thanks in advance!
André

Airtable will accept strings surrounded with single quotes as well as double quotes. In your case, you can search for a double quote by surrounding it with single quotes, like this.

IF(LEFT({text}, 1)='"', MID({text}, 2, LEN({text})-2), {text})

Worked like a charm!! Thank you.

Jessica_Hutton
6 - Interface Innovator
6 - Interface Innovator

I think this post has the solution I need. But, I can’t figure out how to apply it to my existing formula.

I have a 4-part title (4 fields concatenated into 1 to make up a title). Not all 4 parts are used in every record, so I wanted my formula to remove added punctuation or spaces in case of a blank field in the concatenated string. I can’t figure out how to keep that aspect of my formula and add the aspect in this forum that gets rid of the unwanted quotation marks when part of the title has a comma.

Here is my current formula:
IF(OR({Agency, Program}=BLANK(),{Module Title}=BLANK()),CONCATENATE({Agency, Program},{Module Title}),CONCATENATE({Agency, Program}," - ",{Module Title}))

Can anyone help me adapt this to get rid of the quotation marks, please? Thank you!!!

This could be done by wrapping your existing formula inside a SUBSTITUTE() function. If all you need to do is remove quotes, this will work:

SUBSTITUTE(IF(OR({Agency, Program}=BLANK(),{Module Title}=BLANK()),CONCATENATE({Agency, Program},{Module Title}),CONCATENATE({Agency, Program}," - ",{Module Title})), '"', "")

If there aren’t any quotes to remove, the output will remain unmodified.

Worked perfectly. Thank you!!!

Hey Justin!

I have the same problem as Jessica, I did follow the steps you guided but I am still having the quotations.
image

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

@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
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

image

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

@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?

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.

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?

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.)
ready-made-formulas-screenshot8-linkedRecordNoExtraQuotes

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

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.

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
6 - Interface Innovator
6 - Interface Innovator

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:
Screen Shot 2021-09-18 at 9.23.26 AM

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}
    ), '"', ""
)