Lookup within formula, how to remove "" ?

[SpaceDandy]

Hey there,

Thank you for reading me !

I have a small issue while using lookup fields within a formula.

I've got 3 differents tables : projects, actions, animations

A projet is made of several actions which are also each made of several animations

The project primary field is a formula : Year / Name (2024 / Orange)

The action primary field is a formula : Project primary field lookup / Action Name (2024 / Orange / soccer)

The animation primary field is a formula : Action primary field lookup / Date (2024 / Orange / soccer / 12/12)

My issue is that in action & animation primary fields, some unnecessary "" are added, i get :

Action primary field : "2024 / Orange" / Soccer
Animation primary field : """2024 / Orange" / Soccer"" / 12/12

I have tried a few formulas but I don't get how I can remove them. Any ideas ?

Thank you for your time, have a nice day !

[Mike_AutomaticN · Accepted solution]

Hey @SpaceDandy!

I've been there.. The reason why Airtable is adding such "" is given that probably the value being looked up might contain a coma ( , ) or similar. As arrays themselves are comma separated values, to highlight the fact that one of the values contains a comma but should still be treated as a whole value within the array, Airtable will contain the full value (comma included) within the quotations.

To get rid of such quotations, you'll want to use the Substitute() formula.

To understand how this works, you gan first give this formula a try, referencing your primary field:

SUBSTITUTE(
    {Animation},
    '"',
    ""
)

You might then also want to include the substitute formula together with your concatenation!

e.g.

DATETIME_FORMAT({Début}, 'DD/MM') & " - " & SUBSTITUTE(Actions, '"', "")

Please let me know if that solves your issue!

Mike, Consultant @ Automatic Nation

[SpaceDandy]

Yes that's perfect, I could have thought about it actually. Thanks a lot ! 😉