My guess: most likely because somewhere in your data there is an instance of ["]. For example to indicate a size in inches…

Thank you for your guess. Yes, that would be my first suspicion as well. But, as I’ve already stated, that is not the case. That has been double-checked, triple checked, and then reconfirmed… There is NO quotation mark anywhere in any field involved in this curious event. It only happens with one specific product, say Widgets. When Widgets is the product, I get the ( " ) in the formula. There is no explanation by looking at the fields being concatenated and the formula works in all other products.

I can have a look if you want…

Thanks, but I’ve resolved it by removing the offending LEFT({SelectKit},5) field from my formula. I still have no explanation, but I am 100% absolute that there is no quotation mark in any of my fields, so it was being arbitrarily generated by code. Why, I do not know, but clearly not an expected result.

If you include a concatenated field from a linked table in your “New concatenate field”, the value of that field will be enclosed with quotation marks. To avoid these marks the easy way, simply add the original fields via lookup rather than the concatenated value from a linked table.


Example: In my table containing the lookup field called “Full Name” that concatenations the values of First Name and Last Name fields in my linked table. I use my concatenated field to display the value in my table but I add the original fields as lookups and use the following formula to create my “New’ concatenate field, then I simply hide the First Name and Last Name fields.


CONCATENATE({Last Name}&”."&{First Name}&"."&‘Proposal’) will desplay Doe.John.Proposal


CONCATENATE({FullName}&"."&‘Proposal’’) will display as “Doe,John”.Proposal (quotes are because FullName is already a concatenated field).

There must be something else going on in your base, as that’s not my experience, at all.

I just created a scratch base along the lines you describe: {First Name}, {Last Name}, and {Full Name}, where {Full Name} = CONCATENATE({First Name},' ',{Last Name}). I then use a lookup field to pull {Full Name} to my main table, where I concatenate it with '.' and 'proposal'. The resulting text does not have any double quotes – nor could I get it to generate any extraneous quotation marks, try as I might.

I thought it might have something to do with an embedded comma, but that line of query went nowhere, as well. Right now, I can’t say where your quotes are coming from — just that they don’t appear to be the result of looking up a concatenated field…

BTW, your concatenation syntax is sort of a suspenders-and-belt situation, in that if you use the &-sign method, you don’t need to wrap everything in CONCATENATE(). That is to say,

{First Name}&'.'&{Last Name}

does the same thing as

CONCATENATE({First Name},'.',{Last Name})

Hello,

I had a similar issue and have found the culprit.

When attempting to stringify a lookup field, if that lookup field has a comma in it’s string, Airtable places that entire string in double quotes when displayed.

I assume this has something to do with the way Airtable delimits records by commas.

So, in my formula, here is a depiction of the oddity. Note: Account is a linked record field.

CONCATENATE({Name}, " ", {Last Name} , " - ", {Account})

Record from Accounts table

Resulting String concat.

And when the comma is removed from the Accounts Name field…

The result is…

You can quickly test this by adding a formula field that is simply your lookup field.

{Account}

This result is, again, a string wrapped in double quotes.

Hope this is helpful!

@Tyler_Yocca is right, the commas are the culprit. Commas are reserved in database parlance for separating items in an array, so Airtable is apparently interpreting the string as an array of items separated by commas in the background of those formulas, and then adding quotes to indicate that it has recast what it handled as an array in the formula back to a string.

Kind of weird, but I’m pretty sure that’s what’s going on.

It does make sense but you’d think Airtable would have a way of resolving it…it really messes up HTTP request integrations when you are trying to search on a specific field and the data coming in is something like “Test, this, string”. If you need to match that in the Airtable database you would have a difficult time.

May be worth requesting support from Airtable.

I also recently ran into a case of unexpected quotation marks in copy/pasted data. In this instance the culprit was an extra space character between the parts of a two-word field name. To make matters even more confusing, in grid view the doubled character is invisible, as white space ordinarily is collapsed automatically by the browser. Only if one expanded the record did the additional space become even perceivable.

Caproic Acid rendered as Caproic Acid",1


but Caproic Acid would render as Caproic Acid

This thread was very helpful, so thank you everyone.

If like me there is a reason why you can’t change the use of commas in a text field, you can use the following simple formula to remove the double quotation marks.

SUBSTITUTE(,’"’,"")

SUBSTITUTE(Field,’"’,"") didn’t work for me but this did—SUBSTITUTE(Field,""","")

I’m having trouble getting this to work for me. The name of my concatenated field that I’m trying to get rid of the quotes from is, “Agency, Program, Module.” I have tried the below combinations based on these comments, but neither worked. Do you know what I’m doing wrong? The error message I receive says it’s an invalid formula and so it doesn’t save.

SUBSTITUTE({Agency, Program, Module},’"’,"")


SUBSTITUTE({Agency, Program, Module},""","")

@Jessica_Hutton


This worked for me; try copying and pasting my formula as displayed below. You may have gotten bit by the Airtable formula editor’s dislike for curly quotes, which often get carried over if copying and pasting formatted text, such as text not inside a code block on these forums.

SUBSTITUTE(Name, '"', '')

SUBSTITUTE(Name, '"', '')

That worked great!! Thank you!!

I cant seem to get this to work its giving me an error. Im trying to apply the formula to the lookup field via fx

Figured it out, it was returning error becasue I was trying to modify the string as a fx of LOOKUP. Thanks!

SUBSTITUTE(Name, '"', '')

This worked! thank you

@Casey_Kleiman & @Sam_Aronson This is an issue I’m currently having with commas and quotation marks… where do you insert that substitution text in your formula? Mine currently reads:

DATETIME_FORMAT({Date of Donation}, ‘M/D/YYYY’) & " — " & IF({Donor Account}, {Donor Account},{Company account})

And it’s pulling like this:

Can you provide a larger screen shot that shows both the cell values and the field names at the tops of the columns for all the fields included in the formula?

Here you go. Sorry, I had to censor some of this because it’s private information. The whited-out column is dollar amounts. See how Farrar, Straus & Giroux" is the only one adding quotations to the name?

Thanks for the screen capture. No worries about the censored info. The screen capture shows that the {Company account} field is a linked record field. The original field in the other table may be a text field, but in this table, the field type is a linked record field.

You can use one of the formulas that I provided in the other thread.

Okay amazing. Can I be a total newbie and ask WHERE to put this formula? I added it to the end of my formula and it says it’s invalid:

DATETIME_FORMAT({Date of Donation}, ‘M/D/YYYY’) & " — " & IF({Donor Account}, {Donor Account},{Company account}) SUBSTITUTE({Company account}, , )

Put it in place of the field name. I have broken up your formula into multiple lines to make it easier to read.

DATETIME_FORMAT({Date of Donation}, 'M/D/YYYY') 


& " — " & 


IF({Donor Account}, 


  {Donor Account},


  SUBSTITUTE({Company account}, '"', '')


)


 

Incredible. Thank you for your patience/explaining!