Skip to main content
Solved

CONCATENATE with prefix for multiple values in the column

A

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

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

    Like this?

    If so, here’s the formula:

    IF(Colours, "Colour_" & SUBSTITUTE(Colours & "", ", ", ", Colour_"))
    A
    C
    I
    5 people like this
    • A
      Agora_Orbis
    • C
      christoph
    • I
      Il_Ballo_del_Do
    • J
      Jeremy_Oglesby
    • E
      Ed_Lee
    A
    • Agora_Orbis
    • 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. :nerd_face:

    Thank you very much !!

    J
    1 person likes this
    • J
      Jeremy_Oglesby
    I
    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


    Most helpful members this week

    Terms & Conditions