【エクセル】データの結合方法

質問

エクセルで、各セルにあるデータを次の内容で結合したいのですが、方法が分からないので、
ご教示お願い致します。

＜結合前＞
A1|A3|A7|A10|A11|A12
BC1|BC4|BC7|BC10|BC11|BC12|BC15

※"|"はセルの区分けのつもりです。

＜結合後＞
A1,3,7,10,11,12
BC1,3,7,10~12,15

結合前のデータのルールは、
・英字+数字の組合せ。
・英字は１文字もしくは複数文字。
・データが増える際は、セルの右の欄に追加される。
・横に増えるデータの英字は必ず同じ。

結合時のルールは、
・英字は必ず同じなので、一番最初にのみ表すこと。
・データ（数値）の区切りは","（カンマ）で分けること。
・連続したデータ（数値）の場合は、始まりの数値と終わりの数値の間に"~"（チルダ）を入れて、纏めて表示すること。

内容が分かりづらい場合は補足しますので、何卒宜しくお願いします。

回答No.4

okormazd · ベストアンサー

後方に空白セルがあっても、できるようにするというのであれば、下記の1行を追加すればいいでしょう。
これで、下方にフィルできます。

s = s0
i = 1
For Each c In rng
cdat(i) = c.Value

If c.Value = "" Then Exit For　　　　'ここに、この1行を追加する

l = Len(cdat(i))
n = Val(Right(cdat(i), l - l0))
cdat(i) = n & ","

回答No.3

okormazd

ユーザー定義関数です。
結合したい範囲を選択してください。選択範囲は、1列、または1行です。
cdatで20個までにしておきましたが、配列添え字を増やせばいいです。

既定のworksheet関数の組み合わせでは、作業列を使っても難しいでしょう。
原始的に書いただけなので、もっといい方法があるかもしれません。
エラー処理などはしていません。

Function test(rng As Range) As String

Dim cdat(20) As String, i As Integer, i0 As Integer
Dim l As Integer, l0 As Integer, s0 As String, s As String
Dim c As Range, n As Integer, n0 As Integer
Dim f As Integer

f = 0

l = Len(rng(1))
For i = 1 To l
s = Mid(rng(1), i, 1)
If Asc(s) >= 48 And Asc(s) < 58 Then
l0 = i - 1
s0 = Left(rng(1), l0)
Exit For
End If
Next

s = s0
i = 1
For Each c In rng
cdat(i) = c.Value
l = Len(cdat(i))
n = Val(Right(cdat(i), l - l0))
cdat(i) = n & ","
If i > 1 Then
If n = n0 + 1 Then
cdat(i) = "~" & cdat(i)
cdat(i - 1) = Left(cdat(i - 1), Len(cdat(i - 1)) - 1)
f = f + 1
If f > 1 Then
cdat(i - 1) = ""
End If
Else
f = 0
End If
End If
n0 = n
i = i + 1
Next

i0 = i - 1
For i = 1 To i0
s = s & cdat(i)
Next

s = Left(s, Len(s) - 1)
test = s

End Function

補足

ご回答ありがとうございます。

回答頂いた内容を組み込んだところ、ひとまず思い通りの結果が取り出せました。
ですが、セルを的確に設定しなければならない事から、データ量が変化すると対応出来ませんでした。

データ量（A1|A2...の量）を範囲指定して、
数値が入っていないセルは飛ばす。
上記の様な処理は出来ないでしょうか。

ご教示宜しくお願い致します。

回答No.2

imogasi

エクセル関数には、文字列の文字部分と数字部分を簡単に分ける1発の関数が無い。
VBAでも同じ。ですから関数でやると複雑な式になる。
一方質問要求内容が珍奇で、それも関数式でやれば複雑化する要因である。
それでVBAでやってみた。
エクセルで少し変わったことをやろうとすると（特に表の「組み換え）
VBAを使わないと出来ないか関数が大変複雑化する。
私は少し変わったことをするにはVBAが必須というのが持論です。
下記は平凡な陳腐なやり方で出来ている。
Sub test02()
Dim sh1, sh2
Set sh1 = Worksheets("Sheet4")
Set sh2 = Worksheets("Sheet5")
K = 2
d = sh1.Range("A65536").End(xlUp).Row
For i = 2 To d
r = sh1.Cells(i, 255).End(xlToLeft).Column
' MsgBox "r=" & r
For j = 1 To r
x = sh1.Cells(i, j)
If j = 1 Then
s = x
GoTo p1
End If
For K = 1 To Len(x)
y = Mid(x, K, 1)
If Asc(y) >= 49 And Asc(y) <= 57 Then
Exit For
End If
Next K
'MsgBox i & j & "=" & k '数字文字の始まり位置
'MsgBox i & j & "=" & Mid(x, k, Len(x) - k + 1) '数字部分
s = s & "," & Mid(x, K, Len(x) - K + 1)
p1:
Next j
MsgBox s
sh2.Cells(K, "A") = s
K = K + 1
Next i
End Sub
例データ
Sheet4　質問のデータ
結果
Sheet5　A2:A3
A1,3,7,10,11,12
BC1,4,7,10,11,12,15
ーー
B列以右の列の文字部分がA列のそれと同じか、などのチェックは省いている。

補足

ご回答ありがとうございます。

VBAに関してはど素人でして、上手く組み込めていません。
標準モジュールで回答頂いた内容を貼り付けてみましたがダメでした。
(Functionで始まる形式で無い為？)

使い方をご教示頂けると大変助かります。
何卒、宜しくお願いします。

回答No.1

某HN クロメート(Chromate)（@CoalTar）

チルダに関してはG列くらいなら何とか作業列を駆使し対応できるかもしれませんが
とりあえず、なし版で9文字まで

A5セル
=LEFT(A1,LEN(A1)-LEN(LOOKUP(9^9,1*RIGHT(A1,{1,2,3,4,5,6,7,8}))))
下へ オートフィル

B5セル
=A5&SUBSTITUTE(TRIM(SUBSTITUTE(A1&B1&C1&D1&E1&F1&G1,A5," "))," ",",")
下へ オートフィル

－－－－－－－－－－－－－－－－－
A9セル
=IF(A1="","",SUBSTITUTE(A1,$A5,"")*1)

A13セル =A9
B13セル =IF(AND(A9+1=B9,B9+1=C9),"~",B9)
G13セル =G9
で力尽きた(^^;
－－－－－－－－－－－－－－－－－

「A」のほうは連続にならないのはなんで？

補足

ご回答ありがとう御座います。
内容を試したところ、私の方でもかなり近いところまで行けました。

>「A」のほうは連続にならないのはなんで？
英字は、品種を指しています。数字はそれに振られた番号になります。

A1|A3|A7|A10|A11|A12
上記の数値以外でも、
A2|A4|A5|A6|A8|A9
と、実際は、間の番号も他の列には出てきます。

データは適当に入力していますので、
データの内容には特に意味は持たせていないつもりです。