Concatenate "eats" my separators inside the fields

[Penny_Schiffer]

Hi all, I would like to combine two text fields into one. Each field has several entries that are separated with a comma (e.g. in one row field 1 contains “Germany, EU, France” and field 2 contains “Italy, Austria”).

When I combine them with {Field1} & {Field2} it deletes all the commas so that it looks like this: “GermanyEUFranceItalyAustria”. How can I keep the commas and just combine the field`s text exactly like it is? Tried already joinarray and putting using {Field1} & ", "& {Field2} but still the same effect.

Thank you!

[Nathalie_Collin]

Hi and welcome @Penny_Schiffer,

It should work in theory. What field types are Field1 & Field2? And if they are linked or referenced somehow to anything, what field type is that linked or referenced field?

I’ve seen this happen before, but I can’t replicate it at the moment. If I recall correctly, it boiled down to field type. In the sample base below, I tried various field types that I could think of and none are replicating what you’re experiencing. Here’s the sample base in case it sheds any light.

Airtable

Concatenate removes Comma - Airtable

Explore the "Concatenate removes Comma" base on Airtable.

[Penny_Schiffer]

Dear @Nathalie_Collins thanks for the answer. The fields are referenced from another database as Lookup. And they have been referenced onto that other database also as a look up (ideally, I would have liked to go two levels with my lookup). The original field from which the values are taken is a multiselect field.

In your sample base you created a copy of the field, made it an auto select and then concatenated them, correct? Is it possible to do this while keeping the link between the original column and the copy?

[Justin_Barrett]

A lookup field creates an array (i.e. a collection) of values. For display purposes, Airtable separates them with commas, but internally, there are no commas in the data. It’s just a collection of individual strings. By default when concatenating an array with another string, Airtable mashes all of the array’s strings together into one big string before concatenating it with your new content. To keep the separation, use the ARRAYJOIN function, like so:

ARRAYJOIN({Lookup field}, ", ") & " - My new content"

BTW, several other field types also create arrays: rollups, links, etc. Any place you can potentially have multiple items in a single field, you’ll likely have an array.

[Nathalie_Collin]

@Penny_Schiffer,

I’m now able to replicate your issue. Look in table SUMMARY/ALL COUNTRIES field

I changed the original base. Have a look again.

Sheet 1 - LOCATIONS
Sheet 2 - EVENTS
Sheet 3 - SUMMARY (replication of error)

So, EVENTS are linked to LOCATIONS and SUMMARY is linked to EVENTS

Solution, in my opinion is to eliminate the 2nd linkage. Have a look at how I accomplished this on EVENTS SOLUTION

Airtable

Concatenate removes Comma - Airtable

Explore the "Concatenate removes Comma" base on Airtable.

[Nathalie_Collin]

Oops @Justin_Barrett, we both replied at the same time. Both @Penny_Schiffer and I tried the Arrayjoin function and it didn’t work. Perhaps, I’m entering it in wrong.

[Justin_Barrett]

In your example, the formula you use for the {All Countries} field converts the lookup array into a string, so the ARRAYJOIN in the next field doesn’t operate as it should.

Each array has to be filtered through ARRAYJOIN separately. If you have more than one array that you want to combine, the resulting joins have to be manually combined with a concatenate. The formula for your {All Countries} field should be this:

ARRAYJOIN({Phase 1 Countries Lookup}, ", ") & ", " & ARRAYJOIN({Phase 2 Countries Lookup}, ", ")

[Nathalie_Collin]

You’re the best @Justin_Barrett :smiling_face_with_three_hearts: :grinning_face_with_big_eyes:

Arrays have always been a sore point for me. I can’t wrap my head around it.

[Penny_Schiffer]

Hi @Nathalie_Collins and @Justin_Barrett, thank you for your help. I will check it out tomorrow and report back to you if it worked! :grinning_face_with_smiling_eyes:

Update: I couldn’t wait until tomorrow. I checked it with the joinarray and it worked fine. Thank you for the quick and competent advice!

[Robert_Matysze2]

Hey Guys, thanks for this topic. I’m looking to merge two columns where I have url plus name and surname in one piece. Tried concatenate or arroyjoin but that connects me only first name.

My formula is to merge the google mail search link + name and surname. I can’t put links here in this post. Where name is Robert Matyszewski - picks an only first name.

What should I do?