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

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

列全体を指定する時のRangeとColumnsの違い|ツイッター出題回答 (2023-09-24)
シートのActiveXチェックボックスの指定方法|ツイッター出題回答 (2023-09-24)
ByRef引数の型が一致しません。|ツイッター出題回答 (2023-09-22)
シートコピー後のアクティブシートは何か|ツイッター出題回答 (2023-09-19)
Excel関数の引数を省略した場合について|ツイッター出題回答 (2023-09-14)
セル個数を返すRange.CountLargeプロパティとは|VBA技術解説(2023-09-08)
記号を繰り返してグラフ作成(10単位で折り返す)|ツイッター出題回答 (2023-08-28)
シートを削除:不定数のシート名に対応|VBAサンプル集(2023-08-24)
ランクによりボイントを付ける(同順位はポイントを分割)|ツイッター出題回答 (2023-08-22)
OneDrive使用時のThisWorkbook.Pathの扱い方|VBA技術解説(2023-07-26)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.繰り返し処理(For Next)|VBA入門
4.変数宣言のDimとデータ型|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.マクロとは?VBAとは?VBAでできること|VBA入門
7.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
8.並べ替え(Sort)|VBA入門
9.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
10.条件分岐(IF)|VBA入門




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


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



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