CONCATENATE Function formatting text entries inconsistently

[Daniel_MacPhee]

I am trying to use the CONCATENATE Function to create a primary field entry that returns something that looks like:
Lot-123b234 [Product Variety]

Seems pretty straightforward, but, the resulting entries come back with variable text formatting that seems to be related to the syntax of the text in one of the fields I am using.
For instance:
(This is good) --> if a particular entry in a text field being concatenated is Product Variety it works fine,
(This is bad) --> if a particular entry happens to have a Variety, Product syntax, the CONCATENATE Function result always shows “Variety, Product” (with the quotation marks magically appearing instead of just showing Variety, Product as I want it to)

What am I doing wrong? How can I convince the computer not to add quotation marks where I have not typed them myself? I need to retain the flexibility of the concatenated field to have text entries with both types of syntax and I don’t want the CONCATENATE Formula result to add quotation marks at all–and certainly not just for some of them!

Here’s the formula I’m using:
CONCATENATE({Lot Number}, " [" ,{Crop}, “]”)

Case 1. If Lot Number = Lot-123 and Crop = Hot Pepper
CONCATENATE({Lot-123}, " [" ,{Hot Pepper}, “]”) = Lot-123 [Hot Pepper]

Case 2. If Lot Number = Lot-123 and Crop = Pepper, Hot
CONCATENATE({Lot-123}, " [" ,{Pepper, Hot}, “]”) = Lot-123 [“Pepper, Hot”]

I want Case 2 to come out Lot-123 [Pepper, Hot]

I fear that this might have some connection to the fact that {Crop} field is actually a linked field that connects to a text field in a different table… maybe? or…?

Thanks for the guidance!

[Justin_Barrett]

Welcome to the community, @Daniel_MacPhee! :grinning_face_with_big_eyes: It’s not the CONCATENATE() function that’s causing the problem.

Daniel_MacPhee:

I fear that this might have some connection to the fact that {Crop} field is actually a linked field that connects to a text field in a different table… maybe?

Yup. That’s the reason. Here’s why…

Link fields have the capability of linking to multiple records in another table. Even when you’ve set the field options to only allow you to link to one record, that doesn’t change how the link field treats the data.

When you have multiple links and want to pull that data into a formula, Airtable will turn the collection of links into a single string, separating the linked items with commas. For example, if you had linked to three records in your {Crop} field, each named as a different color, the result would look like this:

However, when the data itself contains a comma, Airtable can’t just leave it as-is because you wouldn’t know if the comma is meant to separate data from different linked records, or if it’s part of a single linked record, so it wraps quotes around the single item to make it clear that it’s a single item, not multiple items:

In your case, though, you know for a fact that you’re only linking to a single item, so killing the surrounding quotes would be helpful. Thankfully, it only requires a small tweak to your formula:

CONCATENATE({Lot Number}, " [", SUBSTITUTE(Crop, '"', ""), "]")

FWIW, you can also write that formula without CONCATENATE(), using the & operator between the pieces you want to combine:

{Lot Number} & " [" & SUBSTITUTE(Crop, '"', "") & "]"

[Daniel_MacPhee]

Thank you so much–such a helpful explanation AND solved my problem too!