エクセル関数応用
最後の空白(や指定文字)以降の文字を取り出す

Excel関数の解説、関数サンプルと高等テクニック
最終更新日:2017-04-04

最後の空白(や指定文字)以降の文字を取り出す

いくつかのスペースやハイフンで区切られた文字列から、


最後のスペースやハイフン以降の文字列を取り出します。

A1セルに、
abc def ghi

abc-def-ghi
これらの文字列から、ghiを取り出します。

以下では、見やすいように区切り文字は"-"で説明します。
スペースの場合は、"-"を" "と変更してください。


方法1
=RIGHT(A1,LEN(A1)-FIND("★",SUBSTITUTE(A1,"-","★",LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))))

または
=MID(A1,FIND("★", SUBSTITUTE(A1, "-", "★", LEN(A1) - LEN(SUBSTITUTE(A1, "-", ""))))+1,256)

考え方は、
最後の区切り文字を、使用していない特殊な文字に置換して、その文字位置より後ろを取り出しています。

LEN(A1) - LEN(SUBSTITUTE(A1, "-", ""))
これは、"-"が文字列に含まれる数をになり、この例なら2になります。

SUBSTITUTE(文字列,検索文字列,置換文字列,[置換対象])
この置換対象は、何番目を置換するかの指定をします。
SUBSTITUTE(A1, "-", "★", LEN(A1) - LEN(SUBSTITUTE(A1, "-", "")))
つまり、この例なら
SUBSTITUTE(A1, "-", "★", 2)
ということで、2番目の "-"を "★"に置換します。
結果として、
abc-def★ghi
となります。

最後に、RIGHT関数またはMID関数で置換した文字以降を取り出します。
RIGHT(A1,LEN(A1)-FIND("★", "abc-def★ghi"))
MID(A1,FIND("★", "abc-def★ghi")+1,256)
MID関数の場合は、第三引数は大きめの数値を指定しておけば良いでしょう。


方法2


=SUBSTITUTE(RIGHT(SUBSTITUTE(A1,"-",REPT("-",256)),256),"-","")

考え方は、
区切り文字の1文字を大量(ここでは256個)の文字に置換して、
後ろの文字を取り出すと、最後の区切り文字以降になるので、区切り文字を消せば良いという考えです。

SUBSTITUTE(A1,"-",REPT("-",256))
この結果は、
abc-def---(256個の-)---ghi
となります。

この右側の256文字を取り出します。
RIGHT(SUBSTITUTE(A1,"-",REPT("-",256)),256)
この結果は、
---・・・---ghi
となります。

最後に、"-"を""で置換して消します。
SUBSTITUTE("---・・・---ghi","-","")
この結果、
ghi
が取り出せています。

ここでの256という数値は、適宜変更して構いません。
対象の最大文字数以上なら、50とか100で問題ありません。


実現する方法はいろいろと考えられるのではないかと思います。
このような数式を作るときは、パズルのつもりで楽しんで作ってみると良いかもしれません。




同じテーマ「エクセル関数応用」の記事

VLOOKUP 左側の列を取得(MATCH,INDEX,OFFSET)
SUMIF関数の良くある間違い
論理式とは条件式とは(IF関数,AND関数,OR関数)
先頭の数値、最後の数値を取り出す
最後の空白(や指定文字)以降の文字を取り出す
SUMIFの間違いによるパフォーマンスの低下について
数値範囲で表検索するVLOOKUP近似一致
エクセルの日付と時刻のまとめ
連続数値部分を取り出し記号で連結
指数近似/対数近似/累乗近似(掲載順位とCTR)
ジャンプ・セル選択の応用(空欄を埋める、1行置きに挿入)


新着記事NEW ・・・新着記事一覧を見る

import文(パッケージ・モジュールのインポート)|Python入門(9月24日)
例外処理(try文)とexception一覧|Python入門(9月23日)
リスト内包表記|Python入門(9月22日)
Pythonの引数は参照渡しだが・・・|Python入門(9月21日)
lambda(ラムダ式、無名関数)と三項演算子|Python入門(9月20日)
関数内関数(関数のネスト)とスコープ|Python入門(9月18日)
関数の定義(def文)と引数|Python入門(9月18日)
組み込み関数一覧|Python入門(9月17日)
辞書(dict型)|Python入門(9月16日)
入力規則への貼り付けを禁止する|VBA技術解説(9月16日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
7.繰り返し処理(For Next)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ