Search code examples
excelexcel-formula

Concatenate and separate column values, if they exist

I have a table with 3 columns which may or may not have values ​​separated by a comma. For example:

Column_1 Column_2 Column_3
A C
D, E F
A X Z
Z

Basically, the result I am looking for is that the following cell contains, for each case:

[X]A, [X]C

[X]D, [X]E, [X]F

[X]A, [X]X, [X]Z

[X]Z

Being [X] a fixed value that I add to each case.

Is it possible to do it with a standard Excel formula?

Solution

  • I understand you're trying to format your data so that each value in your table, including those separated by commas, is prefixed with [X]. You can definitely do this with an Excel formula. Here's how you can achieve that:

    1. Set Up Your Data:

    Let's say your data is in columns A, B, and C, starting from row 2 (A2, B2, C2, etc.).

    1. Use This Formula:

    In a new column (let's use column D), start from D2 and enter this formula:

    =TEXTJOIN(", ", TRUE, IF(A2<>"", "[X]" & SUBSTITUTE(A2, ",", ", [X]"), ""), IF(B2<>"", "[X]" & SUBSTITUTE(B2, ",", ", [X]"), ""), IF(C2<>"", "[X]" & SUBSTITUTE(C2, ",", ", [X]"), ""))

    1. Drag the Formula Down:

    Drag the formula down from D2 to match the rows with your data.