エクセル関数 A#BB#CCCを分割したい

質問

A1に"A#BB#CCC"という文字列があり、#を元にB1 C1 D1に分割させたいのですが、ご教授お願いいたします。B1は書けたのですが、#が同一文字で判別方法などがよくわかりません。
可能であれば、A列が空白になるまでの、マクロ登録をしておきたいです。

B1 = LEFT(A1,FIND("#",A1)-1)

回答No.5

kagakusuki · ベストアンサー

御質問内容が、関数で処理したいのか、マクロで処理したいのかはっきりしていないため、取り敢えず両方の方法を回答しておく事に致します。

【関数を使う方法】
　元の文字列が例えば「#A#BB#CCC#DDDD#EEEEE」等の様になっている場合の様に、分割する列の数が3列ではない場合にも有効な方法です。
　まず、B1セルに次の関数を入力して下さい。

=IF($A1="","",IF(LEN($A1)-LEN(SUBSTITUTE($A1,"#",))+1<COLUMNS($B:B),"",REPLACE(LEFT($A1,FIND(CHAR(1),SUBSTITUTE($A1&"#","#",CHAR(1),COLUMNS($B:B)))-1),1,FIND(CHAR(1),SUBSTITUTE("#"&$A1,"#",CHAR(1),COLUMNS($B:B)))-1,)))

　次に、B1セルをコピーして、B1セルよりも右側にあるセルに「分割した値を表示させるのに必要な列」の全てに貼り付けて下さい。
　次に、1行目において関数が入力されているセル範囲をコピーして、同じ列範囲の2行目以下に貼り付けて下さい。

【VBAのマクロを使う方法】
　以下の様なマクロとなります。

Sub QNo9016125_エクセル関数_A♯BB♯CCCを分割したい()

Dim FirstRow As Long, LastRow As Long, OrigColumn As String, PasteColumn As String

FirstRow = 1
OrigColumn = "A"
PasteColumn = "B"
LastRow = Range(OrigColumn & Rows.Count).End(xlUp).Row
If LastRow <= FirstRow And Range(OrigColumn & FirstRow).Value = "" Then
MsgBox "データがありません。" & vbCrLf & "マクロを終了します。", _
vbInformation, "データ無し"
Exit Sub
End If
With Range(PasteColumn & FirstRow & ":" & PasteColumn & LastRow)
.Value = .Offset(, Columns(OrigColumn).Column - Columns(PasteColumn).Column).Value
.TextToColumns DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Semicolon:=False, Tab:=False, Comma:=False, _
Space:=False, Other:=True, OtherChar:="#"
End With

End Sub

お礼

ありがとうございます。
どちらも、きれいに分割することができました。

理解するのには時間が掛かりそうですが、特にマクロは今後のために勉強させてもらいます！

回答No.8

Chiquilin

#6です。

さっきの式 CLEAN関数で囲むの忘れてました。

=CLEAN(MID(SUBSTITUTE($A1,"#",REPT(CHAR(9),99)),COLUMN(A1)*99-98,99))

お礼

ご留意くださりありがとうございます。
バージョンは２０１３でした。

綺麗に返すことができました。（関数は理解できておりませんが（汗））
また、質問しないで済むように学習しておきたいと思います。
ありがとうございました。

回答No.7

MackyNo1

関数で対応するなら、以下の数式を入力して右方向にオートフィルするのが簡単かもしれません。

=TRIM(MID(SUBSTITUTE($A1,"#",REPT(" ",100)),(COLUMN(A1)-1)*100+1,100))

回答No.6

Chiquilin

質問する時は Excelのバージョンを必ず書きましょう。
Excel2013かどうかで選択できる手段に違いがあります。

A1セルに「A#BB#CCC」と入っているなら

■B1セル
=MID(SUBSTITUTE($A1,"#",REPT(CHAR(9),99)),COLUMN(A1)*99-98,99)
右方向にコピー

回答No.4

chie65536（@chie65535）

追記。

「直前に取り出した文字の長さを利用する」と言う方法を応用すると、３つ以上に切る事ができます。

B1：=LEFT(A1,FIND("#",A1)-1)
C1：=LEFT(RIGHT(A1,LEN(A1)-LEN(B1)-1),FIND("#",RIGHT(A1,LEN(A1)-LEN(B1)-1))-1)
D1：=LEFT(RIGHT(A1,LEN(A1)-LEN(B1&C1)-2),FIND("#",RIGHT(A1,LEN(A1)-LEN(B1&C1)-2))-1)
E1：=LEFT(RIGHT(A1,LEN(A1)-LEN(B1&C1&D1)-3),FIND("#",RIGHT(A1,LEN(A1)-LEN(B1&C1&D1)-3))-1)
（中略）
J1：=LEFT(RIGHT(A1,LEN(A1)-LEN(B1&C1&D1&E1&F1&G1&H1&I1)-8),FIND("#",RIGHT(A1,LEN(A1)-LEN(B1&C1&D1&E1&F1&G1&H1&I1)-8))-1)
K1：=LEFT(RIGHT(A1,LEN(A1)-LEN(B1&C1&D1&E1&F1&G1&H1&I1&J1)-9),FIND("#",RIGHT(A1,LEN(A1)-LEN(B1&C1&D1&E1&F1&G1&H1&I1&J)-9))-1)
（中略）
Z1：=RIGHT(A1,LEN(A1)-LEN(B1&C1&D1&E1&F1&G1&H1&I1&J1……&W1&X1&Y1)-24)

最初のB1と最後のZ1だけ、式が特殊になりますが、途中のC1～Y1は「同じパターン」で出来ます。

残念ながら「#の数が可変」には出来ません。その場合は、マクロで関数を自作しないといけません。

お礼

ご留意ありがとうございます。
沢山の回答を頂き、感激です。
数式の仕組みは、chieさんのご回答が一番わかりやすかったです。
次に質問しないで済むように、ここで学習しておこうと思います！

回答No.3

m_and_dmp

「A」の文字数が1, BB の文字数が2, CCCの文字数が3 と決まっているなら、
B1=Left(A1,1)
C1=Mid(A1,3,2)
D1=Right(A1,3)
しかし、B1 = LEFT(A1,FIND("#",A1)-1) を使うところをみると、A の文字数が変化するのですか？そうなら、Left, Mid, Right で区切るのは難しいと思います

エクセルの「区切り位置」という機能を使うといいでしょう。
A1から下へ、A#BB#CCC 構造の文字列が複数行にわたって記入されているものとします。
・A1～Ax の範囲を選択して、
・メニューバーの「データ」をクリックし、開いた絵ニューの中から「区切り位置」をクリック、
・カンマやタブなどの区切り文字によって・・・をチェックして「次へ」、
・区切り文字は、「その他」をチェックして、ボックスに「＃」を記入し、
・データのプレビューを見て、
　A　BB　CCC と区切り線で分割されていることを確認して「次へ」、
・変わった画面で「完了」

以上で、A1にA, B1にBB, C1にCCC となります。
A列に元のデータを残して置きたいなら、A列のデータをB列にコピーし、B列で上記の手順を実行します。

Left() で処理した場合、A列の元データを書き換えるとB列の内容が自動的に書き換わります。
「区切り位置」で処理した場合は、元データがなくなりますので、元データを書き換えて、それをB列、C列、D列に反映させることはできません。これでは困る場合はマクロを使うしかないでしょう。

お礼

すごくわかりやすい解説をしていただき、ありがとうございます。
説明が足りませんでしたね、すみません。
それぞれの文字数は、文字列で不定でした。

今回は、区切り位置を使わずに処理することができましたので、次の機会に利用させてもらいます。

回答No.2

chie65536（@chie65535）

３つに分割する場合のみ、有効です。

B1：=LEFT(A1,FIND("#",A1)-1)
C1：=MID(A1,FIND("#",A1)+1,FIND("#",A1,FIND("#",A1)+1)-FIND("#",A1)-1)
D1：=MID(A1,FIND("#",A1,FIND("#",A1)+1)+1,LEN(A1))

お礼

ありがとうございます。
返すことができました♪

回答No.1

Prome_Lin

今、試してみましたが、ご希望どおり、動作しています。

Sub Sample()
Dim s As Variant
s = Split(Range("A1").Value, "#")
Range("B1").Value = s(0)
Range("C1").Value = s(1)
Range("D1").Value = s(2)
End Sub

「Split()」関数は、見ればわかると思いますが、指定した文字で全体の文字列を分割し、配列変数に格納する関数です。

「s」は、配列変数ですが、「Dim s(9)」などとはしません。

また、この配列には、数値が入るのか、文字列が入るのか、分割した結果は分かりませんので（たいていは、csvファイルに使う）、「Variant」を指定しておいて、どんなデータにも対応できるようにしておきます。

必ず、文字しかない、とか、絶対に数値だけ、というのんから、「As String」や「As Integer」などでもよいと思います。

お礼

ありがとうございます。お礼が夜になってしまい申し訳ありません。
配列を使うとこんなにシンプルに書けるのですね。
大変勉強になりました！