Help

CONCATENATE with prefix for multiple values in the column

Solved
Jump to Solution
2842 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Agora_Orbis
5 - Automation Enthusiast
5 - Automation Enthusiast

To any formula ninjas,

A column has multiple keywords/values. I need to add a pre-fix before each of the values.

From:
column: Colour
values: White, Black, Blue

To: add the prefix “Colour_” to each one of the values in the colour
values: Colour_White, Colour_Black, Colour_Blue

I created another column with the prefix Colour_, and then a basic concat, but the result is (of course):
Colour_White, Black, Blue

I don’t know how to add the prefix to each of the values individually, and if concat is the right formula.

Detail: not all columns will have a value, so need to consider an empty cell.

If anyone has a good idea… much appreciate it !
Christie

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

Like this?

10%20PM

If so, here’s the formula:

IF(Colours, "Colour_" & SUBSTITUTE(Colours & "", ", ", ", Colour_"))

See Solution in Thread

3 Replies 3
Justin_Barrett
18 - Pluto
18 - Pluto

Like this?

10%20PM

If so, here’s the formula:

IF(Colours, "Colour_" & SUBSTITUTE(Colours & "", ", ", ", Colour_"))

You clever devil - that’s it. :nerd_face:

Thank you very much !!

how can I modify the formula to append a suffix instead of a prefix?