Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Replace multiple strings in a field with different strings

Topic Labels: Formulas
Solved
Jump to Solution
2969 4
cancel
Showing results for 
Search instead for 
Did you mean: 

I'm trying replace the following text with different values within [] with values from single select columns

[art medium], [main objects], [attribute], [expression], [key light], [detailing], [camera shot], [media], [engine], [artist]

I thought this would be fairly easy, but somehow can't get it to work.

I tried a Substitute() like this:

IF(Medium = BLANK(),
SUBSTITUTE(Label, "[art medium], ", "" ),
SUBSTITUTE(Label, "[art medium]", Medium)
) &
IF({Main objects}=BLANK(),
SUBSTITUTE(Label, "[main objects], ", "" ),
SUBSTITUTE(Label, "[main objects]", {Main objects})
)
 
But it subs the text and then renders the remaining text. 
Does anyone have any pointers how I best go about this?
1 Solution

Accepted Solutions

Ah, right, so sorry, I understand now
 
Could you try:

SUBSTITUTE(
  SUBSTITUTE(
    Label, 
    "[main objects]", 
    {Main Objects}
  ), 
  "[art medium]", 
  {Art Medium}
)

Which will give you:

Screenshot 2023-01-28 at 10.28.17 PM.png

Thank you for your patience explaining this

 

See Solution in Thread

4 Replies 4
TheTimeSavingCo
18 - Pluto
18 - Pluto

Hm, I think if you just did:

 

SUBSTITUTE(Label, "[art medium]", Medium) & "," & SUBSTITUTE(Label, "[main objects]", {Main objects})

 

That might work fine actually?

Here's an example, and the code I'm using is:

 

SUBSTITUTE(
  Label,
  '[medium]', 
  Medium
)

 

Screenshot 2023-01-26 at 9.45.50 PM.png

I think I didn't explain very well the first time around

I have a Single Line Text column with a default the default text :

[art medium], [main objects], [attribute], [expression], [key light], [detailing], [camera shot], [media], [engine], [artist]

The words between the brackets is placeholders for values coming from other columns.

As mentioned I tried this

IF(Medium = BLANK(),
 SUBSTITUTE(Label, "[art medium], ", "" ),
 SUBSTITUTE(Label, "[art medium]", Medium)
) &
IF({Main objects}=BLANK(),
 SUBSTITUTE(Label, "[main objects], ", "" ),
 SUBSTITUTE(Label, "[main objects]", {Main objects})
)
 

I tested it with the following:

Screenshot 2023-01-27 at 09.43.52.png

But as I discovered it gives the following output:

digital art, [main objects], [attribute], [expression], [key light], [detailing], [camera shot], [media], [engine], [artist][art medium], House, [attribute], [expression], [key light], [detailing], [camera shot], [media], [engine], [artist]

Whereas I was hoping for something more:

digital art, House, [attribute], [expression], [key light], [detailing], [camera shot], [media], [engine], [artist]

I definitely makes sense this is happening, SUBSTITUTE(Label, "[art medium]", Medium) only replaces the [art medium] bit and does not take the others out. But I'm not sure if there is another way around this?

 

 

Ah, right, so sorry, I understand now
 
Could you try:

SUBSTITUTE(
  SUBSTITUTE(
    Label, 
    "[main objects]", 
    {Main Objects}
  ), 
  "[art medium]", 
  {Art Medium}
)

Which will give you:

Screenshot 2023-01-28 at 10.28.17 PM.png

Thank you for your patience explaining this

 

Well played, with the nesting of the substitue()... It works.

Thank you for taking me through it.