Eliminating unwanted quotation marks

Hi @Dani_Oliver - had this same issue the other day. In my case, my equivalent of your “Donor Account” field was a multi-select. You don’t say what field type you have but I think you might also get this behaviour for a linked field and possibly others. Here’s what I did:

I created another field “product type as string” using the formula:

SUBSTITUTE({Product Type} & '', ', ', '-')

This takes the multi-select value and turns it into a string:

{Product Type} & ''

Then I use “substitute” on this, replacing “comma-space” with "-".

Then in my other formula field I reference the “product type as string” field rather than the “product type” field (I hide the “as string” field too).

Ah, I don’t think this is quite what I’m dealing with? I may be missing the connection. (But this is super clever on your end!)

Mine is merely text, not multi-select or linked field. The issue is that for some reason, when text is generated in a formula and it has commas already included in the field, it includes the quotation marks when it’s being pulled in a formula. I want to get rid of those, not add an additional field to reference. (There are only a couple that are like this, so it would be redundant for me to create a second field.)

Is this the “No Quotes Formula” what you want?

A simple formula (no internal quotes)

If the original text will only have commas, and no quote marks, a simple solution is to simply remove the quote marks.

SUBSTITUTE({Linked Record}, '"', '') 

A formula for linked records with internal quotes

However, if the original text might have quotes, the previous formula will remove too many quotes. Instead, use a more robust formula that will remove only the extra quotes, keeping any original quotes in place.

IF(LEFT({Linked Record}, 1) = '"',
  SUBSTITUTE(
    MID({Linked Record}, 2, LEN({Linked Record}) - 2),
    '""', 
    '"'
  ),
  {Linked Record}
)

How it Works

First the formula checks if the first characters is a quote mark. If it is not a quote marks, the formula returns the value of the linked text. However, if the first character is a quote, the formula removes the quotes from the beginning and end of the text with the MID function. The formula also converts any double quotes back to single quotes with the SUBSTITUTE function.

You can see the working demo on my website.

SUBSTITUTE({Linked Record}, '"', '') 

IF(LEFT({Linked Record}, 1) = '"',
  SUBSTITUTE(
    MID({Linked Record}, 2, LEN({Linked Record}) - 2),
    '""', 
    '"'
  ),
  {Linked Record}
)

Maybe? It’s not a linked record, it’s just text. It looks like it’s an issue with how AirTable treats commas when pulling into formulas. It’s the issue outlined in this thread, but I’m having trouble understanding where to drop the substitution into my existing formula to strip it of the unnecessary commas:

Years later, here I am having this same issue. It seems some quotation marks are added when the field the formula is calling for is already a lookup field. In my case, my simple formula (

SUBSTITUTE({Linked Record}, '"', '') 

IF(LEFT({Linked Record}, 1) = '"',
  SUBSTITUTE(
    MID({Linked Record}, 2, LEN({Linked Record}) - 2),
    '""', 
    '"'
  ),
  {Linked Record}
)

This was a great, clear response! Thanks for the help 4 years later, LOL. Cheers!