エクセルで特定の文字だけ抽出する関数

質問

A列に、『a-b-c d』といった文字列があります。
a、b、c、dはそれぞれ英数字で、文字数が各行異なり、
aとb、bとcの間には必ず”-”で区切られ、dの前はスペースで区切られています。

またこの文字列は、以下の文字列の場合もあります。
『a-b　d』（cの文字列が無い）
『a　d』（b、cの文字列が無い）
『d』（a、b、cの文字列が無い）

この条件の上で、aの文字列のみをB列、bの文字列のみをC列、cの文字列のみをD列に
表示するには、どういった関数の組み方をすれば良いのでしょうか。
LEFT、MID、RIGHT、FIND、、、そのあたりだと思うのですが、
”-”（ハイフン）が複数存在する時の、（）の中の組み方が分かりません。

分かる方がいましたらお願いします。

回答No.9

某HN クロメート(Chromate)（@CoalTar） · ベストアンサー

こういうものを関数で行う場合、作業列を使うととても易しくになりますね。
B2セルに
=IF(ISNUMBER(FIND(" ",A2)),LEFT(A2,MIN(FIND({"-"," "},A2&"-"))-1),"")
半角スペースが見つかったら計算。なかったら空白にする。
計算部分は
"-"と" "を左から検索して、先に見つかったところ-1 までの文字を返す。

配列定数が分かりにくい場合、
=IF(ISNUMBER(FIND(" ",A2)),LEFT(A2,MIN(FIND("-",A2&"-"),FIND(" ",A2))-1),"")
でも同じ

作業列のC2セル
=RIGHT(A2,LEN(A2)-LEN(B2)-1)
aの部分+1文字分を消す。

D2セルは B2セルをコピペ
E2セルも C2セルをコピペ
E2セルも B2(D2)セルをコピペ
B2:F2セルを選択し、下へオートフィル

作業列は非表示にしておけばよいでしょう
添付図参照 参考まで

回答No.13

maron--5

◆文字列が1つのときは、「a」か「d」かが区別できません、
◆dの前はスペースで区切られています。
◆このことから「a」は「a」とし、「_d」のように前に半角スペースがあれば「d」とすると、
B1=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1," ",REPT(" ",100*(3-(LEN($A1)-LEN(SUBSTITUTE($A1,"-",)))))),"-",REPT(" ",100)),100*COLUMN(A1)-99,100))
★下にコピー

回答No.12

maron--5

B1=IF(OR(COUNTIF($A1,"*-*"),COUNTIF($A1,"* *")),TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1," ",REPT(" ",100*(3-(LEN($A1)-LEN(SUBSTITUTE($A1,"-",)))))),"-",REPT(" ",100)),100*COLUMN(A1)-99,100)),TRIM(MID(REPT(" ",400)&$A1,100*COLUMN(A1),100)))
★右と下にコピー

回答No.11

MarcoRossiItaly

CoalTar様、補足ありがとうございました。ただ、同一文字列が含まれているかどうかが一定していない場合、C1セルのSUBSTITUTEの第4引数は「2」ではなくて可変にしないといけないので、式がもう少し長くなってしまいそうです。そのような対応が必要であると、質問者さんから具体的な説明・相談があれば、修正案を考えたいと思います。同一文字列を含む行数がわずかであれば、数式による対応は別にいらないという場合もあるでしょう。

回答No.10

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

#4 MarcoRossiItalyさんへ
＞＞使用するセルの範囲が多いのでちょっと、
は
>a～dの4種類の文字列どうしで全く同一のものが含まれている場合は、置換によってグチャグチャになります。
の意見かもしれないです。つまり、データ数が多いからチェックできないということかもしれません。
対策は SUBSTITUTE関数にあります。
B2セル =SUBSTITUTE(SUBSTITUTE(F2,G2,"",1),"-","",1)
C2セル =SUBSTITUTE(SUBSTITUTE(G2,H2,"",1),"-","",1)
とすればよいでしょう。

回答No.8

MarcoRossiItaly

＞使用するセルの範囲が多いのでちょっと

作業列をたくさん用いるのがExcelなど表計算ソフトの「まっとうな」使い方です。ワークシートには列がたくさん用意されているのですから、それを使わなければ、Excelは本領を発揮することができません。是非、こういう方法を覚えられることをお勧めします。

作業列が表示されているのが気に食わないのであれば、見せたくない列を非表示にすることができます。あるいは、作業列だけ別シートに分けても、計算には何の問題もありません。

回答No.7

kagakusuki

ANo.5です。
　もし、dの文字列も表示させる場合には、D2セルに次の関数を入力してから、D2セルをコピーして、D3以下に貼り付けて下さい。

=IF(ISNUMBER(FIND(" ",INDEX($A:$A,ROW()))),REPLACE(INDEX($A:$A,ROW()),1,FIND(" ",INDEX($A:$A,ROW())),),INDEX($A:$A,ROW())&"")

回答No.6

K Kazz（@JazzCorp）

細かいトコをイロイロ定義（宣言）してからやらないと、直ぐにまた修正主義者になるだけですね？
項目はどこから来たもの？
入力（これはない筈？）、引用、それとも生成（これもない筈？）？？
a、b、cの中にブランクがある？
dの中に「-」がある？
デリミタの連続はある？
１項目だけ、「d」と「a」はどうやって区別する？

回答No.5

kagakusuki

今仮に、元の文字列がA2以下に入力されているものとします。

　まず、B2セルに次の関数を入力して下さい。

=IF(ISNUMBER(FIND(" ",$A2)),MID($A2,FIND("★",SUBSTITUTE("-"&LEFT($A2,FIND(" ",$A2&" ")-1)&"--","-","★",COLUMNS($B:B))),FIND("★",SUBSTITUTE(LEFT($A2,FIND(" ",$A2&" ")-1)&"---","-","★",COLUMNS($B:B)))-FIND("★",SUBSTITUTE("-"&LEFT($A2,FIND(" ",$A2&" ")-1)&"--","-","★",COLUMNS($B:B)))),"")

　そして、B2セルをコピーして、B～D列の２行目以下のセル範囲に貼り付けて下さい。
　これで、B～D列にa～cの文字列が自動的に表示されます。

　尚、Excelの関数は、参照先のセルに対して切り取り、削除、挿入等を行った場合、セルの参照先がずれてしまうために、正しい計算処理の結果を得られなくなる事が普通なのですが、B2列に入力する関数を以下の様に改良しますと、A列のセルに対して、切り取り、削除、挿入等を行って、セルの位置関係が上下方向にずれてしまった場合でも、正しい結果が得られる様になります。（セルの位置関係が横方向にずれた場合に対しては、対応しておりません）

=IF(ISNUMBER(FIND(" ",INDEX($A:$A,ROW()))),MID(INDEX($A:$A,ROW()),FIND("★",SUBSTITUTE("-"&LEFT(INDEX($A:$A,ROW()),FIND(" ",INDEX($A:$A,ROW())&" ")-1)&"--","-","★",COLUMNS($B:B))),FIND("★",SUBSTITUTE(LEFT(INDEX($A:$A,ROW()),FIND(" ",INDEX($A:$A,ROW())&" ")-1)&"---","-","★",COLUMNS($B:B)))-FIND("★",SUBSTITUTE("-"&LEFT(INDEX($A:$A,ROW()),FIND(" ",INDEX($A:$A,ROW())&" ")-1)&"--","-","★",COLUMNS($B:B)))),"")

お礼

うまくできました、ありがとうございました。

回答No.4

MarcoRossiItaly

a～dの各文字列の文字数が1文字とか決まっているならラクですが、そうではないってことですよね？

先に、全角と半角のスペースを、どちらか一方のみに統一しましょう。置換の機能を使えば一瞬です。例えば半角スペースにするとします。A列全体を選択した状態で、Ctrl+H（置換のダイアログを起動）、「検索する文字列」ボックスに「　」（全角スペース）を、「置換後の文字列」ボックスに「 」（半角スペース）を入力し、「すべて置換」ボタンを押すだけ。

「-」も全角と半角が混じっているなら、同様に統一しておいてください。

置換が終わったら、次式を入力。全ての列を入力しないと、どこかの列でエラーが出ます。

B1　=substitute(substitute(f1,g1,),"-",)
C1　=substitute(substitute(g1,h1,),"-",)
D1　=h1
E1　=substitute(substitute(a1,f1,)," ",)
F1　=if(countif(a1,"* *"),left(a1,find(" ",a1)-1),"")
G1　=if(countif(f1,"*-*"),right(f1,len(f1)-find("-",f1)),"")
H1　=if(countif(g1,"*-*"),right(g1,len(g1)-find("-",g1)),"")

※注意1
a～dの4種類の文字列どうしで全く同一のものが含まれている場合は、置換によってグチャグチャになります。例えばbとcが同一とか、そんな場合です。そういうデータは別途、処理する必要があります。
※注意2
「" "」と「""」は異なるので、区別してください。

お礼

ありがとうございます。使用するセルの範囲が多いのでちょっと、でもありがとうござました。

回答No.3

mu2011

参考です。
関数は繰り返し処理が不得手なのでマクロは如何でしょうか。
対象シートタブ上で右クリック→コードの表示→サンプルコード貼り付け→シート上でAlt＋F8キー押下→sample実行

Sub sample()
Dim i As Long, j As Long, wk
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
wk = Split(Replace(Cells(i, 1), "-", " "), " ")
For j = 0 To UBound(wk) - 1
Cells(i, j + 2) = wk(j)
Next
Next
End Sub

お礼

マクロは使わない方向で考えているので、、、でもありがとうございました。