Skip to main content
Solved

CONCATENATE with prefix for multiple values in the column


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

Best answer by Justin_Barrett

Like this?



If so, here’s the formula:


IF(Colours, "Colour_" & SUBSTITUTE(Colours & "", ", ", ", Colour_"))
View original
Did this topic help you find an answer to your question?

3 replies

  • Inspiring
  • 4647 replies
  • Answer
  • October 16, 2019

Like this?



If so, here’s the formula:


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

  • Author
  • Participating Frequently
  • 5 replies
  • October 16, 2019
Justin_Barrett wrote:

Like this?



If so, here’s the formula:


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

You clever devil - that’s it. 🤓


Thank you very much !!


Justin_Barrett wrote:

Like this?



If so, here’s the formula:


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

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


Reply