Excel 特定の文字列が連続で続いたら非表示

質問

セル内の特定の文字列が2個以上連続してる場合、連続している文字列を非表示にしたい。

セルに横一列にデータを入力し、=C2&","&D2&","&E2&","& ,,,,,
横の列にはデータを","を入れて文字列を結合したセルを表示させます。
例　データの文字列が1 2 3 4 5と入力されていたら
1,2,3,4,5,,,,
と表示され複数セルの入力された文字列が結合した形で表示されます。
これを
1,2,3,4,5
とカンマ","が2個以上連続したらデータの(この場合5)後ろの連続しているカンマを非表示にしたいです。

Excelは2016で、使用できる関数が限られています。
この場合、他の関数の組み合わせで1,2,3,4,5と連続するカンマを非表示にする方法はありますでしょうか。

Excelのシートは添付画像の通りです。
詳しい方いましたら、ご教授下さい。
よろしくお願い致します。

回答No.1

kkkkkm · ベストアンサー

2行目の結果を表示したいセルに
=SUBSTITUTE(TRIM(C2&" "&D2&" "&E2&" "&F2&" "&G2&" "&H2&" "&I2&" "&J2&" "&K2)," ",",")
として必要なだけ下にコピーしてみてください。

お礼

非常に助かりました。 文字列結合時に空欄箇所がある場合に活用しようと思います。

Powered by GRATICA

補足

迅速な回答、有難うございます。
隣の行に関数をコピペしたところ、",,"連続したカンマが消え、1,2,3,4,5表示になりました。

=SUBSTITUTE(TRIM(C2&" "&D2&" "&E2&" "&F2&" "&G2&" "&H2&" "&I2&" "&J2&" "&K2)," ",",")

文字置換のSUBSTITUTE関数と不要スペース削除をするTRIM関数を組み合わせているようですが、
検索文字列が" "で置換文字列が削除する","なのはなぜでしょうか。
SUBSTITUTE関数、TRIM関数を確認しているのですが、なぜ重複しているカンマを消すことが出来るのでしょうか。
TRIM(C2&" "&D2&" "&E2&" "&F2&" "&G2&" "&H2&" "&I2&" "&J2&" "&K2)と文字列の同士の間にカンマではなくスペースをかましているのも気になりました。

カンマを消したいという悩みは解消したのですが、理屈がいまいち掴めていないです。
ご教授い頂けたら幸いです。

回答No.5

kkkkkm

> 結合時に連続したカンマを削除できるようにあえて、空白で結合させて、トリム後、カンマに置換するということでしょうか。

そうですね。今回の場合は、TRIM関数で空白を削除するパターンが余計なカンマを削除する結果と一致したという事です。

回答No.4

kkkkkm

回答No.3一部訂正です。

途中の空白は削除しませんから
↓
途中の空白は一個だけの場合は削除せず、複数連続した場合は一個だけ残して削除しますから

回答No.3

kkkkkm

> 検索文字列が" "で置換文字列が削除する","なのはなぜでしょうか。

TRIM関数は、文字列の前後の空白を削除し途中の空白は削除しませんから

C2&" "&D2&" "&E2&" "&F2&" "&G2&" "&H2&" "&I2&" "&J2&" "&K2
これで
2行目のデータは(vを空白と考えてください)
1v2v3v4v5vvvv
となり
TRIMで5より後ろの空白が削除されて
1v2v3v4v5
となったデータの空白を「,」に置換して
1,2,3,4,5
となります。

補足

補足ありがとうございます。
TRIM関数は、文字列の前後の空白を削除し途中の空白は削除しませんから
＞＞
結合時に連続したカンマを削除できるようにあえて、空白で結合させて、トリム後、カンマに置換するということでしょうか。

回答No.2

iyonohanamuko

物凄くむつこい関数ですが、
M2セルに
=IF(RIGHT(SUBSTITUTE(L2,REPT(",",LEN(L2)-LEN(SUBSTITUTE(L2,",,",""))),""),1)=",",LEFT(SUBSTITUTE(L2,REPT(",",LEN(L2)-LEN(SUBSTITUTE(L2,",,",""))),""),LEN(SUBSTITUTE(L2,REPT(",",LEN(L2)-LEN(SUBSTITUTE(L2,",,",""))),""))-1),SUBSTITUTE(L2,REPT(",",LEN(L2)-LEN(SUBSTITUTE(L2,",,",""))),""))

あとは下にオートフィルでいかがでしょうか？
VBAを構築するのが手っ取り早いとは思うのですが・・・。