For example, if I have 2 cells that contain
a1:(aa;bb;cc;dd;ee)
b1:(cc;dd;ee;ff;gg)
Is there a formula to show (cc;dd;ee) in one cell? Strings in a cell are separated by ";"
Asked
Active
Viewed 54 times
0
-
1Yes, there is a formula! Have you tried anything yet? You got `TEXTJOIN()` and [`FILTERXML()`](https://stackoverflow.com/a/61837697/9758194) available? Are paranthesis part of string or no? Can there be dups in a single cell or are all values unique? – JvdV Jul 12 '22 at 06:52
-
Please provide enough code so others can better understand or reproduce the problem. – Community Jul 12 '22 at 20:02
1 Answers
0
Steps are listed here:
In step4: Array formula below: Keep in mind that after you finished writing the formula in the cell, press (Ctrl + Shift + Enter) otherwise your formula will give value error(#VALUE!). It is an array formula.
=CONCAT("(",TEXTJOIN(";",TRUE,IF(C10=C11:G11,TEXTJOIN(";",TRUE,C10),IF(D10=C11:G11,TEXTJOIN(";",TRUE,D10),IF(E10=C11:G11,TEXTJOIN(";",TRUE,E10),IF(F10=C11:G11,TEXTJOIN(";",TRUE,F10),IF(G10=C11:G11,TEXTJOIN(";",TRUE,G10),"")))))),")")
Ozan Sen
- 2,477
- 2
- 4
- 16
-
OP probably wants to compare the strings in cells `A1` and `B1` and return the matching substring(s) in e.g. cell `C1`. It is unclear though, what should happen if one of the strings contains `...cc;ee;dd...` instead. – VBasic2008 Jul 12 '22 at 10:06
-
-