Excelで社名と名前を分けたい

ひとつのセルに会社名と名前がそれぞれ入っています。

株式会社 a物流山本一郎
b物流株式会社 橋本二郎
株式会社cライン 藤本 三郎
b物流株式会社角川四郎

の様に、会社名と名前の間に空白が必ずあるわけでなく、またセル内で改行してるわけでもありません。
ただ、会社名は10種類ほどで、同じ会社で株式会社の前後の空白などの規則性は同じです。(株式会社 a物流は株式会社a物流の様に空白がないとかはない。)

それを、別のシートに、A列には会社名、B列には氏名と分けたいのです。

別のシートに会社名の一覧を作っておいて、この中に当てはまる文字は会社名だからA列、それ以外の文字はB列に、ただし氏名の一文字めに空白がある場合は空白は省略みたいなことってできますか？

Excelは2010。Windows7です。

これでうまく伝わっているか自信がないですが、よろしくお願いします。

質問者からの補足コメント

• 送られてきたリストが、会社名と名前と合わさったものがひとつのセルに入っていたので、振り分けをしたいです。
  
  会社名はリストがあります。
  名前は沢山あって、これからも増えるので、リストなしでできればと思います。
  
  会社名と名前の間に空白のあるのとないのがあるので、例えば名前を取り出すときに、「会社名以外の右全部」の場合でも一文字に空白が出てしまわないようにしたいです。名前と苗字の間は、そのままでも大丈夫です。
  
  できれば数式でお願いします。

  No.1の回答に寄せられた補足コメントです。 補足日時：2018/06/14 07:03

No.4 ベストアンサー 回答者： fujillin 回答日時： 2018/06/14 10:09

こんにちは

会社名のリストがどこかにあるものとします。
（添付の例では、とりあえず$E$2:$E$4にしてあります。）
後々の為に、これに名前を付けておきます。仮に「社名リスト」としました。
（後でリストの増減があった際などに、対応が簡単になります）

添付図ではA列の元データをB列：会社名、C列：氏名に分ける例です。
分けられない場合は、11行目のように元データと同じものがB列に、C列は空白となります。

B2セルに
　=IFERROR(INDEX(社名リスト,SMALL(IF(社名リスト=LEFT(A2,LEN(社名リスト)),ROW(社名リスト)),1)-1),A2)
の式を入れ、Ctr+Shift+Enterで確定（配列数式です）
C2セルに　=TRIM(MID(A2,LEN(B2)+1,LEN(A2)))
の式を入れて、B2：C2セルの式を下方にフィルコピーしています。

この回答へのお礼

回答ありがとうございます。
できました！すごい！
リストの増減にも対応くださり、分けられない場合も見分けられる。
本当に助かりました。ありがとうございます。

お礼日時：2018/06/14 11:40

No.3 回答者： taisuke555 回答日時： 2018/06/14 09:20

シート「会社名」のA列に会社名を入力し

=TRIM(SUBSTITUTE(A1,会社名!A1:A65,""))
でどうですか？

うまくいかなかったらごめんなさい。

この回答へのお礼

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

お礼日時：2018/06/14 11:37

No.2 回答者： めぐみん_ 回答日時： 2018/06/14 08:26

No.1です。

ん～、数式ご希望ですか。
数式は苦手なのです。（複数の関数を組み合わせるような物は）
それよりもVBAを使うようにしてしまったので。

申し訳ありません。

この回答へのお礼

説明が不足していてすみませんでした。
VBA、使いたいのですが、まだまだ勉強不足です。
いずれチャレンジしていきたいです。
ありがとうございました。

お礼日時：2018/06/14 11:33

No.1 回答者： めぐみん_ 回答日時： 2018/06/14 06:01

＞ただし氏名の一文字めに空白がある場合は空白は省略みたいなことってできますか？

氏名の１文字目と会社名の最後との違いは何なのでしょうか？
言いたいのは『苗字と名前の間』ではなくて？

あとこれって数式ですか？
事前に１０種類ほどの会社名のリストはありますか？

この回答へのお礼

迅速に対応下さって、ありがとうございました。
おかげで、説明不足な点も補足ができました。
ありがとうございました。

お礼日時：2018/06/14 11:34