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

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 ・・・新着記事一覧を見る

トランザクション処理|SQL入門(12月11日)
インデックスを作成して高速化(CREATE INDEX)|SQL入門(12月9日)
他のテーブルのデータで追加/更新/削除|SQL入門(12月8日)
データの削除(DELETE)|SQL入門(12月7日)
データの更新(UPDATE)|SQL入門(12月6日)
複数のSELECT結果を統合(UNION,UNION ALL)|SQL入門(12月5日)
テーブルを結合して取得(INNER JOIN,OUTER JOIN)|SQL入門(12月4日)
データベースの正規化とマスタの作成|SQL入門(12月3日)
データベースにおけるNULLの扱い方|SQL入門(12月2日)
オブジェクト変数とは何か|VBA技術解説(12月2日)


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

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



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

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


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



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