エクセル関数超技 | 最後の空白(や指定文字)以降の文字を取り出す | 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で問題ありません。


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




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

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

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

数値範囲で表検索するVLOOKUP近似一致|エクセル関数超技(10月5日)
エクセルVBAでのシート指定方法|VBA技術解説(9月8日)
VBAのクラスとは(Class,Property,Get,Let,Set)|VBA技術解説(8月28日)
VBAこれだけは覚えておきたい必須基本例文10|VBA技術解説(8月22日)
VBAの省略可能な記述について|ExcelマクロVBA技術解説(8月11日)
複数条件判定を行う時のコツ|ExcelマクロVBA技術解説(7月11日)
For Next の使い方いろいろ|VBA技術解説(6月14日)
VBAを定型文で覚えよう|ExcelマクロVBA技術解説(3月26日)
VBAスタンダード試験対策まとめ|MOS VBAエキスパート対策(3月16日)
ユーザーフォームとメニューの操作|MOS VBAエキスパート対策(3月14日)

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

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



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

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


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





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

    本文下部へ

    ↑ PAGE TOP