エクセルで大文字を検索し文字を抽出する

エクセルで、セル中の２番目に出てくる大文字を探し、その左側の文字列を抽出したいのですが…。


例
Malay gooseberry Sugar palm fruit

Soursop Nipa palm fruit

Dragon's eye fruit Nectarine



結果
Malay gooseberry

Soursop

Dragon's eye fruit



恥ずかしながらマクロは不案内なので、できましたら、関数で教えていただけるとありがたいです。

よろしくお願いいたします。

No.6 ベストアンサー 回答者： mike_g 回答日時： 2010/11/16 15:26

[回答番号：No.2この回答へのお礼]へのコメント、

文字列の左端が必ず大文字(Capital letter)であるならば、次の式を試してみてください。

=LEFT(A1,FIND(" ",A1))&TRIM(LEFT(MID(A1,FIND(" ",A1),99),SMALL(FIND({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O",
"P","Q","R","S","T","U","V","W","X","Y","Z"},MID(A1,FIND(" ",A1),99)&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),1)-1))

この回答へのお礼

再度のご投稿ありがとうございます。


できました!
ありがとうございます!!



皆様のおかげをもちまして、作業を進めることが出来ます。
この場をお借りして、お礼を申し上げます。
また、質問させて頂くと思いますので、今後ともよろしくお願い致します。
ありがとうございましたm(__)m。

お礼日時：2010/11/16 16:38

No.5 回答者： kagakusuki 回答日時： 2010/11/16 14:26

>データが2行目から始まる場合に、うまく出力できませんでした。

　それでしたら、以下の様にされると良いと思います。

B2セルにB1用の数式を入力
　　↓
B2セルをコピーして、B3セルに貼り付け
　　↓
B2セルを右クリック
　　↓
現れた選択肢の中にある[削除]をクリック
　　↓
現れた「削除」ウィンドウの[上方向にシフト]をクリックしてチェックを入れる
　　↓
「削除」ウィンドウの[OK]ボタンをクリック

　これで、B3の数式がB2に移動する筈です。

この回答へのお礼

再度のご投稿ありがとうございます。


ご指示どおりに操作してみたのですが、[Ｂ]列全体が#REF!となってしまいました。

残念です。

勉強させて頂きます。

またよろしくお願い致します。

お礼日時：2010/11/16 16:35

No.4 回答者： kagakusuki 回答日時： 2010/11/16 10:53

　大文字が3個以上含まれている場合にも対応する関数です。

　今仮に、A1セルに元の文字列が存在していて、2番目に現れる大文字よりも、前にある部分の文字列を、B列上のセルに表示するものとします。

　その場合の数式の一例は以下の様になります。

=LEFT($A1,SUMPRODUCT(ROW(OFFSET(B$2,,,LEN($A1)-1))*(CODE(MID($A1,ROW(OFFSET(B$2,,,LEN($A1)-1)),1))>64)*(CODE(MID($A1,ROW(OFFSET(B$2,,,LEN($A1)-1)),1))<91)*EXACT(MID($A1,2,ROW(OFFSET(B$2,,,LEN($A1)-1))-2),LOWER(MID($A1,2,ROW
(OFFSET(B$2,,,LEN($A1)-1))-2))))+IF(EXACT(MID($A1,2,LEN($A1)),LOWER(MID
($A1,2,LEN($A1)))),LEN($A1),-1))

　尚、この数式は、元の文字列の最初の文字が、大文字であるか小文字であるかには関係なく、2文字目以降に現れる最初の大文字よりも、前にある部分の文字列を、取り出しますから、もしも、英文の常識から外れた、小文字から始まる文字列の場合には、最初に現れる大文字よりも、前にある部分の文字列を、取り出す事になります。
　又、元の文字列の2文字目以降に大文字が存在しない場合には、元の文字列の全文が表示される様にしています。
　因みに、元の文字列の2文字目以降に大文字が存在しない場合には、何も表示しない様にする数式は、次の様になります。

=LEFT($A1,SUMPRODUCT(ROW(OFFSET(B$2,,,LEN($A1)-1))*(CODE(MID($A1,ROW(OFFSET(B$2,,,LEN($A1)-1)),1))>64)*(CODE(MID($A1,ROW(OFFSET(B$2,,,LEN($A1)-1)),1))<91)*EXACT(MID($A1,2,ROW(OFFSET(B$2,,,LEN($A1)-1))-2),LOWER(MID($A1,2,ROW
(OFFSET(B$2,,,LEN($A1)-1))-2))))+IF(EXACT(MID($A1,2,LEN($A1)),LOWER(MID
($A1,2,LEN($A1)))),0,-1))


　それから、上記の数式のままでは、元の文字列の文字数が1文字のみの場合と、文字列が無い場合には、エラーになります。
　エラーにならない様にするためには、作業用のセル（ここでは仮にC1セルを使用する事にします）を使用します。

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

=IF(OR(LEN($A1)<2,EXACT(MID($A1,2,LEN($A1)),LOWER(MID($A1,2,LEN($A1))))),LEN($A1),SUMPRODUCT
(ROW(OFFSET(C$2,,,LEN($A1)-1))*(CODE(MID($A1,ROW(OFFSET(CB$2,,,LEN($A1)-1)),1))>64)*(CODE(MID($A1,ROW(OFFSET(C$2,,,LEN($A1)-1)),1))<91)*EXACT(MID($A1,2,ROW(OFFSET(C$2,,,LEN($A1)-1))-2),LOWER(MID($A1,2,ROW
(OFFSET(C$2,,,LEN($A1)-1))-2))))-1)

　次に、取り出した部分を表示するセルに、次の数式を入力して下さい。

=LEFT($A1,$C1)

　これで、1文字以下にも対応する事が出来ます。

この回答へのお礼

いつもご投稿ありがとうございます。

３パターンもの式を教えて頂き、恐縮です。


今回の作業としましては、1番目の式で十分です。
ただ、データが2行目から始まる場合に、うまく出力できませんでした。
データ[Ａ２]の結果が、[Ｂ３]に出力されてしまい、[Ｂ２]には#VALUE!が表示されてしまいます。

自分で直せなくて歯がゆいです。
もしお時間があるようでしたら、さらにご教示頂きたくお願い申し上げます。
よろしくお願い致します。

お礼日時：2010/11/16 13:56

No.3 回答者： mike_g 回答日時： 2010/11/15 23:32

[回答番号：No.2]の修正

2010/11/15 21:20現在、「教えて!goo」から投稿した[回答番号：No.2]は見えていませんが、「OKWave」では見えています。→ お～い、「教えて!goo」のシステム担当者よ、しっかりしてくれ！

それはさておき、私が提示した式中の「"Y"」と「}」の間に「,"Z"」を追加してください。

この回答へのお礼

ご投稿ありがとうございます。
また、お礼が遅くなり、申し訳ありません。

ご投稿頂きました式を貼り付けてみたのですが。
例題には、該当するものがなかったので恐縮なのですが、最初の大文字と2番目に出てくる大文字が同一の場合に、うまく抽出することが出来ません。


例えば、


Malay gooseberry Sugar palmは、Malay gooseberryとなるのですが、

Malay gooseberry Mugar palmは、Malay gooseberry Mugar palmとなってしまいます。



もしお時間があるようでしたら、さらにご教示頂きたくお願い申し上げます。
よろしくお願い致します。

お礼日時：2010/11/16 13:14

No.2 回答者： mike_g 回答日時： 2010/11/15 23:15

=TRIM(LEFT(A1,SMALL(FIND({"A","B","C","D","E","F","G","H","I",

"J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y"}
,A1&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),2)-1))

No.1 回答者： MackyNo1 回答日時： 2010/11/15 23:00

先頭が大文字で始まり、５0文字目までに2度目の大文字が出てくるなら、例えば以下のような関数で表示することができます。

=LEFT(A1,MIN(IF((CODE(MID(A1&REPT(1,50),ROW($A$2:$A$50),1))<=90)*(CODE(MID(A1&REPT(1,50),ROW($A$2:$A$50),1))>=65),ROW($A$2:$A$50)-1,"")))

配列数式ですので、入力後Ctrl+ShiftLEnterで確定してください。

この回答へのお礼

ご投稿ありがとうございます。
また、お礼が遅くなり、申し訳ありません。

ご投稿頂きました式を貼り付けてみたのですが。
例題の場合ですと、Soursop Nipa palm fruitがSoursopと抽出されず、空白になってしまいます。

それと、数列関数も詳しくないので、教えて頂けるとありがたいのですが、
データの範囲設定は、ROW($A$2:$A$50)のところでしょうか？
つまり、データが300行にわたる場合は、ROW($A$2:$A$300)とすればよいのでしょうか？


もしお時間があるようでしたら、さらにご教示頂きたくお願い申し上げます。
よろしくお願い致します。

お礼日時：2010/11/16 13:12