Help

Re: Replace multiple strings in a field with different strings

Solved
Jump to Solution
798 3
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
16 - Uranus
16 - Uranus

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.