Skip to main content
Solved

Concatenate using multiple linked records

  • October 2, 2020
  • 4 replies
  • 44 views
J

Hi!

How do I individually concatenate fields if one of the fields is comma-separated?

For example, I want to take “red, blue” and concatenate this with “shoe” to come up with ““red shoe”,“blue shoe”” with the quotation marks instead of “red, blue shoe”.

Any ideas? Thank you in advance!

Best answer by Zollie

Colors (linked records)
Blue, Red

Item (single line text)
Shoes

Item Colors (formula)
Blue Shoes, Red Shoes

Formula for Item Colors:

ARRAYJOIN(
  {Colors},
  {Item} & ", "
) & " " & {Item}

Note, array related formulas such as ARRAYJOIN are designed for linked records and related fields. They don’t currently support comma separated strings, for example.

4 replies

Z
Forum|alt.badge.img+18
  • Zollie
  • Inspiring
  • Answer
  • October 2, 2020

Colors (linked records)
Blue, Red

Item (single line text)
Shoes

Item Colors (formula)
Blue Shoes, Red Shoes

Formula for Item Colors:

ARRAYJOIN(
  {Colors},
  {Item} & ", "
) & " " & {Item}

Note, array related formulas such as ARRAYJOIN are designed for linked records and related fields. They don’t currently support comma separated strings, for example.

J
Kamille_Parks11
J
  • Justin_Lim
  • Author
  • New Participant
  • October 5, 2020

Colors (linked records)
Blue, Red

Item (single line text)
Shoes

Item Colors (formula)
Blue Shoes, Red Shoes

Formula for Item Colors:

ARRAYJOIN(
  {Colors},
  {Item} & ", "
) & " " & {Item}

Note, array related formulas such as ARRAYJOIN are designed for linked records and related fields. They don’t currently support comma separated strings, for example.


Thanks, Zollie! I tried this with a standard Formula field and it didn’t quite work the way I wanted to - but it worked as expected when I used a Rollup field instead.

Justin_Barrett
Forum|alt.badge.img+21

Here’s another option that will work in a formula field:

SUBSTITUTE(Colors, ", ", " " & Item & ", " ) & " " & Item

Z
M
Forum|alt.badge.img+2
  • Motlaq_AlMutair
  • Participating Frequently
  • May 21, 2023

Here’s another option that will work in a formula field:

SUBSTITUTE(Colors, ", ", " " & Item & ", " ) & " " & Item


This approach worked for me. But how would the formula change if the "item" column also takes more options?

To elaborate, I have two columns: Companies and Keywords. A have 'combined' formula column in the following format: "Company - Keyword". Sometimes, there is more than one company and more than one keyword. So the formula above doesn't always work. Below is an example of the formula's output in different scenarios. 

 

and here is the formula:

 

Terms & ConditionsAccessibility statement