２つの文字列を比較して、その中で共通している部分（の中で最も長い部分）を表示させいたい。そんな質問を掲示板で見かけて、Excel2019だっていうので、それなりに苦労して回答したら、最後の最後に「365でもいいや」みたいな返事が出てきた。いや、それならもっと簡単にできるでしょうが。
例えば、A1の「九州・山陽新幹線みずほ」と、B1の「西九州新幹線かもめ」の場合、共通するのは「九州」と「新幹線」。ただ文字数が多いのは３文字の「新幹線」なので、B1には「新幹線」を表示させる。
そのためには、どちらかの文字列を分解してやる必要がある。A2でやるなら、
=UNIQUE(TOCOL(MID(A2,SEQUENCE(LEN(A2)),SEQUENCE(,LEN(A2)))))
これを FIND関数の検索文字列にして A1と比較し、一致するものだけ表示させる。
=REPT(C2#,ISNUMBER(FIND(C2#,A1)))
SORTBY関数を使って文字列降順にソート。
=SORTBY(D2#,LEN(D2#),-1)
ここまでくれば、先頭の文字列を INDEXか TAKE関数で抜き出してやればいい。
=INDEX(E2#,1)まとめるとこうなる。
=LET(
a,A1,
b,A2,
x,UNIQUE(TOCOL(MID(b,SEQUENCE(LEN(b)),SEQUENCE(,LEN(b))))),
y,REPT(x,ISNUMBER(FIND(x,a))),
IFERROR(INDEX(SORTBY(y,LEN(y),-1),1),"")
)スピルに対応しているバージョンは、結果を表示しながら数式が作れるからいいね。
もし「九州新幹線」が答えとして欲しい時は下記のようにすればいい。
=LET(
a,A1,
b,A2,
x,UNIQUE(TOCOL(MID(b,SEQUENCE(LEN(b)),SEQUENCE(,LEN(b))))),
y,REPT(x,ISNUMBER(FIND(x,a))),
CONCAT(UNIQUE(LEFT(y)))
)