指定文字列を含んだセル内容の抽出

質問

こんばんは。excelで行き詰まってしまいましたのでどうぞご教授下さい。
下のようなデータがあります。

　　　　Ａ　　　Ｂ　　　Ｃ　　　Ｄ　　　　Ｅ　　　Ｆ　　　Ｇ
１　a山岡　中根　山田　a丸山　　山岡　丸山
２　平尾　a松岡　a南田 a大田　　松岡　南田　大田
３
・
・

Ａ列からＤ列までは　名前か、名前の前に"a"がついたデータが入っています。
Ｅ列からＧ列には、各行のＡ列からＤ列のデータ先頭に"a"がついているセルの名前を抽出したいのです。

"a"のついた名前は各行とも０から３つまでです。

いろいろ試してみましたが、うまくいきません。
よろしくお願いいたします。

回答No.3

mu2011 · ベストアンサー

一例です。
E1に次に数式を入力して縦横にコピーして下さい。尚、配列数式の為、入力完了時にshift＋ctrl＋enterキーを同時押下して下さい。
=IF(COUNTIF($A1:$D1,"a*")>COLUMN(A1)-1,SUBSTITUTE(INDEX($A1:$D1,SMALL(IF(NOT(ISERROR(FIND("a",$A1:$D1))),COLUMN($A1:$D1),99999),COLUMN(A1))),"a",""),"")

お礼

すごい。すごすぎます。
数式がワケわからないのですが（笑）まったく要望どおりになりました。
本当にありがとうございました。

回答No.2

maron--5

◆こんな方法もありますよ
E1=IF(COLUMN(A1)>COUNTIF($A1:$D1,"a*"),"",INDEX(SUBSTITUTE($A1:$D1,"a",),SMALL(INDEX(SUBSTITUTE((LEFT($A1:$D1)="a")*1,0,10^5)*COLUMN($A1:$D1),),COLUMN(A1))))
★下にコピー

お礼

皆様の知識にはおどろかされます。
無事解決できました。ありがとうございました。

回答No.1

hallo-2007

少し厄介ですが
　　　　Ａ　　　Ｂ　　　Ｃ　　　Ｄ　　　　Ｅ　　　Ｆ　　　Ｇ　　　Ｈ　　　Ｉ　　Ｊ
１　a山岡　中根　山田　a丸山　　　　　　　　　　　　　　　　　　　山岡　丸山
２　平尾　a松岡　a南田 a大田　　　　　　　　　　　　　　　　　　　松岡　南田　大田
３
・
・
E1セルに　
=IF(LEFT(A1,1)="a",COLUMN(A1),"")
　右へ４つフィル　下フィル　aで始まる名前の列番号が表示
Ｉ1セルに　
=IF(ISERROR(SMALL($E1:$H1,COLUMN(A1))),"",MID(OFFSET($A1,0,SMALL($E1:$H1,COLUMN(A1))-1),2,4))
　右へ３つフィル　下フィルしてみてください。

名前が　Ａ～Ｄ　4列です。増やすときはSMALL($E1:$H1,COLUMN(A1))
の範囲変更してください。

お礼

迅速にご回答いただきましてありがとうございます。
解決できました。ありがとうございました。