|
こんにちは。マクロでないとダメですか?
関数なら
=SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" ")," ",",")
マクロとしても
With Range("E1:E4")
.Formula = _
"=SUBSTITUTE(TRIM(A1&"" ""&B1&"" ""&C1&"" ""&D1&"" ""),"" "","","")"
.Value = .Value
End With
もし、範囲が変動するようなら
Dim rng As Range
Dim s As String
Dim r As Long, c As Long, i As Long
r = 4 '行
c = 4 '列
Set rng = Cells(1, 1).Resize(r, c)
For i = 1 To r
With WorksheetFunction
s = .Trim(Join(.Index(rng.Rows(i).Value, 0), " "))
End With
Cells(i, c + 1).Value = Replace(s, " ", ",")
Next i
Set rng = Nothing
|
|