Skip to main content
Solved

Lookup within formula, how to remove "" ?

  • December 20, 2024
  • 2 replies
  • 51 views
S
Forum|alt.badge.img+6

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 !

 

Best answer by Mike_AutomaticN

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

1 Attachments

2 replies

Mike_AutomaticN
Forum|alt.badge.img+28

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

S
S
Forum|alt.badge.img+6
  • SpaceDandy
  • Author
  • Known Participant
  • December 20, 2024

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


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

Mike_AutomaticN
Terms & ConditionsAccessibility statement