エクセルで空白セルをとばして表示させたい

Question

エクセル２００７です。
表のとおり、E1に「2,3」と空白セルを除いた表示をしたいのですが、どうしたらよろしいでしょうか。関数またはマクロを教えて下さると大変助かります。
=CONCATENATE(A1,",",B1,",",C1,",",D1)だと「,2,3,」と余分な「,」が表示されてしまいます。

ママチャリ · Accepted Answer

横から失礼します。
入力する値が数字だけならば、こんな式でもいけそうです。

=SUBSTITUTE(TRIM(CONCATENATE(A1," ",B1," ",C1," ",D1))," ",",")

上記の式は、各セルの値を空白区切りでCONCATENATEして、それをTRIMしてから、空白をカンマに変換しています。

ただし、ひとつのセルに、”1　2”みたいな入力があると、その空白もカンマになってしまうのですが・・・。

WindFaller · Answer

こんにちは。

Office 365 には、
TEXTJOIN 関数がありますので、それを再現してみました。
https://dekiru.net/article/14504/
同じ名称にはしないように、[TXTJOIN関数]にしました。

TEXTJOIN (区切り記号[配列可能], 空の文字列を無視, 文字列1, 文字列2, ...,)

1.Event が走らないようにしましたが、第三引き数を、制限なしにすることに工夫しました。
添付図の2番めの式は、
=TXTJOIN({"+","="},FALSE,A3:D3)
区切り文字は、配列式が入ります。第三引き数は、ひとつずつ入れる必要がありません。範囲を選択すればよのですが、ひとつずつも入れることが可能です。セルの範囲だけでしたら、以下のコードは、半分で済みますが、これに個別に入れることを想定して処理機能を2つに分けました。私は、当該Office は持っておりませんから、どのぐらい近づけたか、少し気になります。

'//標準モジュールです。
Function TXTJOIN(ByRef delim As Variant, bln As Boolean, ParamArray arg())
''(区切り記号, 空の文字列を無視, 文字列1, 文字列2, ..., )
Dim buf As String
Dim a, c
Dim cnt As Long, i As Long
Dim dl()
Application.EnableEvents = False
If bln = True Then delim = ""
If InStr(1, TypeName(delim), "()") > 0 Then
　cnt = UBound(delim)
　dl = delim
Else
　cnt = 0 'デリミタの種類の数なし
　ReDim dl(1)
　dl(1) = delim
End If
i = 1
For Each a In arg()
　If TypeName(a) = "Range" Then
　 For Each c In a
　　If buf = "" And c.Value <> "" Then
　　 buf = c.Value & dl(i)
　　 i = i + 1
　　ElseIf c.Value <> "" Then
　　 buf = buf & c.Value & dl(i)
　　 i = i + 1
　　End If
　　If cnt = 0 Then
　　 i = 0
　　 dl(i) = delim
　　ElseIf i > cnt Then
　　 dl(cnt) = ""
　　End If
　 Next
　Else
　 If buf = "" And a <> "" Then
　　buf = a & dl(i)
　　i = i + 1
　 ElseIf a <> "" Then
　　buf = buf & a & dl(i)
　　i = i + 1
　 End If
　 If cnt = 0 Then
　　i = 0
　　dl(i) = delim
　 ElseIf i > cnt Then
　　dl(cnt) = ""
　 End If
　End If
Next
'再加工
If cnt = 0 And Len(buf) > 1 Then
　If Right(buf, 1) = delim Then
　 buf = Left(buf, Len(buf) - 1)
　End If
End If
TXTJOIN = buf
Application.EnableEvents = True
End Function

tom04 · Answer

No.2です。

エラーが出ちゃいましたか！
それでは
＞For i = 1 To UsedRange.Rows.Count
をもう少し丁寧に

＞For i = 1 To ActiveSheet.UsedRange.Rows.Count
にしたらどうなりますか？

前回のコードはシートモジュールであれば問題ないと思うのですが、
標準モジュールだとエラーになるかもしれません。m(_ _)m

hirorin38 · Answer

すでに、回答が得られていますが、ちょっと補足します。ご承知の通り、CONCATENATE関数を使うと空白があると余計な","が付きます。 No1さんの回答あるように、=TEXTJOIN(",",TRUE,A1:D1) と書けばよろしいのですが、これはExcelの2016版からできるもので、古いExcelでは対応していません。そのため、Excel 2007では、ユーザ定義関数を利用することが考えられます。 https://www.excelspeedup.com/textjoin2/ TEXTJOINの関数をユーザ定義関数で書くと以下のようになります。 Function TEXTJOIN(Delim, Ignore As Boolean, ParamArray par()) Dim i As Integer Dim tR As Range TEXTJOIN = "" For i = LBound(par) To UBound(par) If TypeName(par(i)) = "Range" Then For Each tR In par(i) If tR.Value <> "" Or Ignore = False Then TEXTJOIN = TEXTJOIN & Delim & tR.Value2 End If Next Else If par(i) <> "" Or Ignore = False Then TEXTJOIN = TEXTJOIN & Delim & par(i) End If End If Next TEXTJOIN = Mid(TEXTJOIN, Len(Delim) + 1) End Function

tom04 · Answer

こんにちは！

VBAでの一例です。

Sub Sample1()
    Dim i As Long, j As Long, myStr As String
        Range("E:E").ClearContents
        For i = 1 To UsedRange.Rows.Count
        For j = 1 To 4
            If Cells(i, j) <> "" Then
                myStr = myStr & Cells(i, j) & ","
            End If
        Next j
            If Len(myStr) > 0 Then
                Cells(i, "E") = Left(myStr, Len(myStr) - 1)
            End If
            myStr = ""
        Next i
End Sub