Skip to main content

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?

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 )

 

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 )

 


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:

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?

 

 

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:

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:



Thank you for your patience explaining 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:



Thank you for your patience explaining this

 


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

Thank you for taking me through it.  

Terms & ConditionsAccessibility statement