一番右のスペース以降の文字列を抽出したい

質問

winXP　Excel2003

外国人の氏名（カタカナ）と敬称が大量に入力されたファイルがあります。
氏名と敬称が同じセルに入っているので、敬称のみを
隣のセルに移したいのですがうまくいきいません。

名前は各国さまざまで、スペースでいくつかに区切られています。
名前の最後にスペース+敬称がついており、敬称の種類はたくさんあります。
（様、氏、閣下、女史）など。

そこで、
「一番右のスペース以降の文字列を抽出」
すればいいかな？と思うのですが、IfやRight、LENなどを
使っても、何が悪いのかエラーが出てしまいます。
区切り位置ウィザードでは、スペース全てで区切られてしまって
氏名がバラバラになってしまいます。

何かいい関数はありませんか？
勉強不足ですみません。宜しくお願いします。
-------------------------------------------
元データ例）
ハリー　ポッター　様
セブルス　スネイプ　閣下
ボガート　氏
ニコラス　ド　ミムジー　ポーピントン　卿

回答No.3

merlionXX · ベストアンサー

様、氏、閣下、女史などの敬称で最長のものが２文字なら、これでいけると思います。
=MID(RIGHT(A1,3),FIND(" ",ASC(RIGHT(A1,3)))+1,2)

もし3文字の敬称もあるなら
=MID(RIGHT(A1,4),FIND(" ",ASC(RIGHT(A1,4)))+1,3)
ただし、これは敬称が１文字でスペースの前の文字列も１文字の人、たとえば、「アイ　ウ　様」　みたいのがいたらダメです。（そんな人いないかな）

お礼

一番うまく行きました！
ありがとうございます！！

三文字の敬称もあるのですが、全体では少ないほうなので
個別で直すことにします。

思っていたより難問で　自力では太刀打ちできませんでした…
本当に助かりました。

回答No.5

merlionXX

不備を修正し、かつ3文字にも対応させました。
=IF(4-LEN(SUBSTITUTE(JIS(RIGHT(A1,4)),"　",""))=1,MID(RIGHT(A1,4),FIND("　",
JIS(RIGHT(A1,4)))+1,3),MID(RIGHT(A1,3),FIND("　",JIS(RIGHT(A1,3)))+1,2))

回答No.4

DOUGLAS_

「配列数式」での計算になります。
　敬称は「妃殿下」であろうが「大統領閣下」であろうが、何文字でも構いません。ただし、中国人の方のように「名前」の方に漢字が入っていたらサッパリダメです。

１）A1:A4 に「元データ例）」が入っているとして、B1 に
=LEFT(A1,SMALL(IF(CODE(MID(A1,ROW(INDIRECT("$A1:$A"& LEN(A1))),1))>12320,ROW(INDIRECT("$A1:$A"& LEN(A1))),LEN(A1)),1)-1)
と入力し、[Shift]・[Ctrl] キーと同時に [Enter] キーを押下して確定します。
　このとき、数式バーには「{ }」で括られた式が入ります（配列数式）。
２）以上で、Ｂ列に敬称以外が入りましたので、今度は、C1 に
=SUBSTITUTE(A1,B1,"")
と入力し、普通に [Enter] で確定します。
３）B1:C1 を選択して、下方向に必要分コピーします。
４）最後にＢ・Ｃ列を選択し、形式を選択して「値」で貼り付けます。

お礼

ありがとうございます!

やってみました。
カタカナの人はうまくできました！
ただ、漢字名もたくさんあったこと、
あとなぜか、エラー（#VALUE!）が出てしまうところがありました

思ったよりも難しい課題で　一人では太刀打ちできませんでした。
ご助言感謝します。ありがとうございました！

回答No.2

_Kyle

少し長くなりますが、組込み関数でもできます。
以下、元の文字列がA1セルにあるとします。

●Ａ案
　最後のスペースを短剣符に置き換えて、短剣符を探す

・最後の全角スペースより後方の文字列（敬称）
　=RIGHT(A1,LEN(A1)-FIND("†",SUBSTITUTE(A1,"　","†",LEN(A1)-LEN(SUBSTITUTE(A1,"　","")))))

・最後の全角スペースより前方の文字列（氏名）
　=LEFT(A1,FIND("†",SUBSTITUTE(A1,"　","†",LEN(A1)-LEN(SUBSTITUTE(A1,"　",""))))-1)

●Ｂ案
　右から１，２，３，４文字目を順次切り出して、スペースであるかどうかを調べる。

敬称部分が３文字以下という前提で。

・最後の全角スペースより後方の文字列（敬称）
　=RIGHT(A1,MATCH("　",LEFT(RIGHT(A1,{1,2,3,4}),1),0)-1)

・最後の全角スペースより前方の文字列（氏名）
　=LEFT(A1,LEN(A1)-MATCH("　",LEFT(RIGHT(A1,{1,2,3,4}),1),0)+1)

ご参考まで。

お礼

思ったよりも難しい課題で　一人では太刀打ちできませんでした。
ご助言感謝します。ありがとうございました！

回答No.1

Sinogi

関数で右から文字を数えるのは見あたらないのでユーザー定義関数にしてみました。

参考までに

Option Explicit
Dim L, A

Function RightStr(MyStr)
L = Len(MyStr)
For A = L To 1 Step -1
If Mid(MyStr, A, 1) = " " Then
RightStr = Mid(MyStr, A + 1, L - A)
Exit For
End If
Next A
End Function

お礼

す、すみません
これをどこに入れたらいいのかわかりませんでした・・・
多分私のやり方が悪いと思うのですが
元データが崩れてしまいました

思ったよりも難しい課題で　一人では太刀打ちできませんでした。
ご助言感謝します。ありがとうございました！