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:
- Set Up Your Data:
Let’s say your data is in columns A, B, and C, starting from row 2 (A2, B2, C2, etc.).
- 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]"), ""))
- Drag the Formula Down:
Drag the formula down from D2 to match the rows with your data.