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

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で問題ありません。


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





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

選択行の色を変える(条件付き書式,Worksheet_SelectionChange)
他ブックを参照できる関数、他ブックを参照できない関数
時間計算で困ったときの確実な対処方法
VLOOKUP 左側の列を取得(MATCH,INDEX,OFFSET)
SUMIF関数の良くある間違い
論理式とは条件式とは(IF関数,AND関数,OR関数)
先頭の数値、最後の数値を取り出す
最後の空白(や指定文字)以降の文字を取り出す
SUMIFの間違いによるパフォーマンスの低下について
数値範囲で表検索するVLOOKUP近似一致
エクセルの日付と時刻のまとめ

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

Excelワークシート関数一覧(2010以降)|VBAリファレンス(4月22日)
クラスとCallByNameとポリモーフィズム(多態性)|VBA技術解説(4月6日)
VBAでのタイマー処理(SetTimer,OnTime)|VBA技術解説(4月3日)
クラスとイベントとマルチプロセス並列処理|VBA技術解説(4月2日)
エクセルの日付と時刻のまとめ|エクセル関数超技(3月6日)
Excelシートの複雑な計算式を解析するVBA|VBAサンプル集(2月18日)
VBAクラスの作り方:独自Rangeっぽいものを作ってみた|VBA技術解説(2月16日)
VBAクラスの作り方:列名のプロパティを自動作成する|VBA技術解説(2月14日)
VBAクラスの作り方:列名の入力支援と列移動対応|VBA技術解説(2月11日)
クラスを使って他ブックのイベントを補足する|VBA技術解説(2月6日)

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

1.ひらがな⇔カタカナの変換|エクセル基本操作
2.最終行の取得(End,Rows.Count)|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.RangeとCellsの使い方|ExcelマクロVBA入門
6.変数とデータ型(Dim)|ExcelマクロVBA入門
7.マクロって何?VBAって何?|ExcelマクロVBA入門
8.繰り返し処理(For Next)|ExcelマクロVBA入門
9.とにかく書いて見よう(Sub,End Sub)|VBA入門
10.セルに文字を入れるとは(Range,Value)|VBA入門



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

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


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






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

    本文下部へ