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?
A Join-Split-Join
=LET(data,A2:C11,delimiter,", ",prefix,"X",
dp,delimiter&prefix,
BYROW(data,LAMBDA(r,LET(
j,TEXTJOIN(delimiter,,IF(ISERROR(r),"",IF(r="","",r))),
IF(j="","",prefix&TEXTJOIN(dp,,TEXTSPLIT(j,delimiter)))))))
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.
Louis Cha is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
3
Another way using Office 365:
=ARRAYTOTEXT("[X]"&TEXTSPLIT(ARRAYTOTEXT(TOROW(A1:C1,1)),", "))
2
Try using the following one single dynamic array formula:
=BYROW(A2:C5,LAMBDA(α,
LET(δ, CONCAT(α&", "),
TEXTJOIN(", ",1,"[X]"&TEXTSPLIT(δ,", ",,1)))))
Explanations:
- Using
BYROW()function which applies a customLAMBDA()calculations to each row in the array to return one single array as output. - So, the custom function applied here, within a
LET()function, which helps in reducing the redundant calculations as well, reduces the use of repeated formulas. - Variable
δis defined toCONCAT(α)all the cells in the rows into one by a delimiter comma. - Next, we are splitting the same using
TEXTSPLIT()function by columnwise and ignoring the blanks which may or may not return. - Finally, wrapping within the
TEXTJOIN()function to wrap the whole with a delimiter comma and a space, along with the splitted output a fixed value[X]has been joined to get the desired output as one single array.
Also, without using the LAMBDA() helper function, could use the following to copy it down :
=TEXTJOIN(", ",1,"[X]"&TEXTSPLIT(CONCAT(TOROW(A2:C2,1)&", "),", ",,1))