えくせるちゅんちゅん

ことりがエクセルをちゅんちゅんするブログ

エクセル文字列処理数式奥義集

エクセル関数で文字列を自由自在に変換するための数式を大公開します。(随時更新)

載せて欲しい式や、バグ等があればコソッと教えてください。

f:id:Kotori-ChunChun:20190519223333p:plain


本記事の数式について

本来セルアドレスとなる部分は次のような記号に置き換えています。

パラメータ 記号 値の例
文字列 _s_ abcde,fghij,lmn,opq,123,,xxx
文字数 _n_ 3
検索文字列 _f_ ,
要素番号 _idx_ 1 (インデックスは1が開始値
日付 _dt_ 2018/12/5 0:00
日付 _ymd_ 20180305

「★」はダミー文字です。もし使用するなら、使用頻度の低い別の文字に変えて下さい。

すぐに検証したい時はセルの名前定義でこの名前を付けると楽です。

文字列処理関数

sの先頭からn文字取り出す

=LEFT(_s_,_n_)

sの末尾からn文字取り出す

=RIGHT(_s_,_n_)

sの先頭からn文字取り除く

=REPLACE(_s_,1,_n_,"")

sの末尾からn文字取り除く

=LEFT(_s_,LEN(_s_)-_n_)

sにfが存在するか判定する

=COUNTIF(_s_,"*"&_f_&"*")>0

sの先頭から最初のfまで取り出す

=LEFT(_s_,FIND(_f_,_s_))

sの先頭から最初のfの前まで取り出す

=LEFT(_s_,FIND(_f_,_s_&_f_)-1)

sの先頭から二番目のfの前まで取り出す

11,22,33,44,55→11,22

=LEFT(_s_,FIND(_f_,_s_,FIND(_f_,_s_)+1)-1)

sの末尾から最初のfまで取り出す

=REPLACE(_s_,1,FIND(_f_,_s_)+(LEN(_f_)-1),"")

sの末尾から最初のfまで取り出す

=RIGHT(_s_,LEN(_s_)-FIND(_f_,_s_)-(LEN(_f_)-1))

sに含まれるfの出現数をカウント

11,22,33,44,55→4、空欄→0、fのみ→1

=(LEN(_s_)-LEN(SUBSTITUTE(_s_,_f_,"")))/LEN(_f_)

sをfで分割したときの要素数

11,22,33,44,55→5、空欄→1、fのみ→2

=(LEN(_s_)-LEN(SUBSTITUTE(_s_,_f_,"")))/LEN(_f_)+1

sのうち一番最後に出現するfの位置(InStrRev)

VBAで言うInStrRev(s,f)に相当。エラーの出方によって数パターン考えた。

S="":#VALUE!、一致無し:#VALUE!、f無し:#DIV/0!

=FIND("★",SUBSTITUTE(_s_,_f_,"★",
     (LEN(_s_)-LEN(SUBSTITUTE(_s_,_f_,"")))/LEN(_f_)
))

S="":0、一致無し:0、f無し:LEN(S)

=SUMPRODUCT((ISNUMBER(
  FIND(_f_,_s_,ROW(INDIRECT("1:"&LEN(_s_))))
))*1)

S="":#REF!、一致無し:#NA!、f無し:LEN(S)

=LOOKUP(LEN(_s_),
  FIND(_f_,_s_,ROW(INDIRECT("1:"&LEN(_s_))))
)

S="":0、一致無し:0、f無し:LEN(S)

=IFERROR(LOOKUP(10^17,
  FIND(_f_,_s_,ROW(INDIRECT("1:"&LEN(_s_))))
),0)

sの先頭から最後のfまで取り出す

=LEFT(_s_,
  FIND("★",SUBSTITUTE(
     _s_,
     _f_,
     "★",
     (LEN(_s_)-LEN(SUBSTITUTE(_s_,_f_,"")))/LEN(_f_)
  ))-1
)

使用例

ファイル、フォルダのパスから一階層登ったパスを取得するのによく使います。

  • ファイルパスからフォルダパスを取得
  • ファイル名から拡張子以外の名前を取得

sの末尾から最後のfまで取り出す

=SUBSTITUTE(
  RIGHT(
    SUBSTITUTE(_s_, _f_, REPT("★", LEN(_s_))),
    LEN(_s_)
  ),
  "★",
  ""
)

使用例

  • ファイルパスからフォルダ名を取得
  • ファイルパス・ファイル名から拡張子を取得

sの先頭からidx番目に出現するfの位置

1,22,333,4444,55555,6の2番目→5

=FIND("★",SUBSTITUTE(_s_,_f_,"★",_idx_))

sの先頭からidx番目のfより前を取り出す

1,22,333,4444,55555,6の4番目→1,22,333,4444

=LEFT(_s_,FIND("★",SUBSTITUTE(_s_&_f_,_f_,"★",_idx_))-1)

sの先頭からidx番目のfより後を取り出す

1,22,333,4444,55555,6の4番目→55555,6

=MID(
  _s_,
  FIND("★",SUBSTITUTE(_s_&REPT(_f_,LEN(_s_)),_f_,"★",_idx_))+LEN(_f_),
  LEN(_s_)
)

sをfで分割してidx番目の要素を取り出す(Split)

VBAで言うSplit(_s_,_f_)(_idx_)に相当

1,22,333,4444,55555,6の4番目→4444

=INDEX(SUBSTITUTE(
  MID(
    SUBSTITUTE(_s_,_f_, REPT("★",LEN(_s_))),
    ROW(INDIRECT("A1:A"&LEN(_s_)))*LEN(_s_)-(LEN(_s_)-1),
    LEN(_s_)
  ),
  "★",
  ""
),_idx_)

こちらも同様だが、配列を使わずに純粋な文字列処理だけで実装したバージョン

=LEFT(
  MID(
    _f_&_s_,
    FIND("★",SUBSTITUTE(_f_&_s_&REPT(_f_,LEN(_s_)),_f_,"★",_idx_))+LEN(_f_),
    LEN(_f_&_s_)
  ),
  FIND(
    _f_,
    MID(_f_&_s_,FIND("★",SUBSTITUTE(_f_&_s_&REPT(_f_,LEN(_s_)),_f_,"★",_idx_))+LEN(_f_),LEN(_f_&_s_))&_f_
  )-1
)

sをfで分割して各項目の文字数がn以下か検証する

たとえば内部改行で構成されたセルが列幅をオーバーしないかの簡易的なチェックなどに。 (内部改行=Alt+Enter=Ctrl+J=CHAR(10)=vbLF)

=MAX(INDEX(
  LEN(
    SUBSTITUTE(
      MID(
        SUBSTITUTE(s, f, REPT("★",LEN(s))),
        ROW(INDIRECT("A1:A"&
          (LEN(s)-LEN(SUBSTITUTE(s,f,"")))/LEN(f)+1
        ))*LEN(s)-(LEN(s)-1),
        LEN(s)
      ),
      "★",
      ""
    )
  ),0
))<=n

sをfで分割して和を求める

=SUMPRODUCT(INT(
    SUBSTITUTE(
      MID(
        SUBSTITUTE(_s_, _f_, REPT("★",LEN(_s_))),
        ROW(INDIRECT("A1:A"&
          (LEN(_s_)-LEN(SUBSTITUTE(_s_,_f_,"")))/LEN(_f_)+1
        ))*LEN(_s_)-(LEN(_s_)-1),
        LEN(_s_)
      ),
      "★",
      ""
    )
))

sをfで分割して和を求める(文字列混入対応CSE版)

a,1,b,2,c,3から,で区切った数値だけを合計する。 CSE版。半角スペース混入厳禁。

{=SUM(
  IFERROR(
    MID(
      SUBSTITUTE(_s_,_f_,REPT(" ",LEN(_s_))),
      ROW(INDIRECT("A1:A"&LEN(_s_)))*LEN(_s_)+1,
      LEN(_s_)
    )+0,0)
)}

sのうち&と=で区切られた項の数値部分の和を求める

_f_ = &_g_ = =

L1=1&L2=2&L3=3&L4=4 → 1+2+3+4 → 10

※分割数固定

=SUMPRODUCT(
  IFERROR(
    MID(
      SUBSTITUTE(
        SUBSTITUTE(_s_,_g_,_f_),
        _f_,
        REPT(" ",LEN(_s_))
      ),
      {0;1;2;3;4;5;6;7;8}*LEN(_s_)+1,
      LEN(_s_)
    )+0,
    0
  )
)

sの先頭の数字部分だけを数値として取り出し

=LOOKUP(10^17,LEFT(_s_,ROW(INDIRECT("1:"&LEN(_s_))))*1)

sに含まれる数字の個数をカウント

=SUMPRODUCT(N(ISNUMBER(-MID(_s_,ROW(INDIRECT("A1:A"&LEN(_s_))),1))))

sの最初に出現する数字1字を取り出す

abc123→1、a→""

=MID(_s_,MIN(FIND({0,1,2,3,4,5,6,7,8,9},_s_&"0123456789")),1)

sの最初に出現する数字以降を取り出す

abc123→123、abc123def→123def、a→""

=MID(_s_,MIN(FIND({0,1,2,3,4,5,6,7,8,9},_s_&"0123456789")),LEN(_s_))

または

=MID(_s_,
  MATCH(
    TRUE,
    INDEX(ISNUMBER(1*MID(_s_,
      ROW(INDIRECT("1:"&LEN(_s_))),1
    )),0),
    0
  ),LEN(_s_)
)

sの最初に出現する数字の終わりまでを取り出す

abc123def→abc123

=IFERROR(LEFT(_s_,
    MATCH(1,
      MMULT(ISNUMBER(-MID(_s_,ROW(INDIRECT("1:"&LEN(_s_)))+{0,1},1))*1,{1;2}),
    0)
  ),"")

sの最初に出現する数字だけを取り出す(数値)

abc123def → 123def → 123

上記の式を組み合わせたバージョン

=LOOKUP(10^17,LEFT(
  MID(_s_,MIN(FIND({0,1,2,3,4,5,6,7,8,9},_s_&0123456789)),LEN(_s_)),
  ROW(INDIRECT("1:"&LEN(_s_)))
)*1)

sの最初に出現する数字だけを取り出す(文字列)

abc123def→123

MMULT関数を使用した高難易度バージョン

=IFERROR(MID(_s_,
    MATCH(2,MMULT(ISNUMBER(-MID(_s_,ROW(INDIRECT("1:"&LEN(_s_)))+{0,1},1))*1,{1;2}),0)
    +1,
    MATCH(1,MMULT(ISNUMBER(-MID(_s_,ROW(INDIRECT("1:"&LEN(_s_)))+{0,1},1))*1,{1;2}),0)
    - MATCH(2,MMULT(ISNUMBER(-MID(_s_,ROW(INDIRECT("1:"&LEN(_s_)))+{0,1},1))*1,{1;2}),0)
  ),"")

sの先頭から数字が出現する前までの文字列を取り出す

abc123def456→abc、a→a、""→""、123→""

=LEFT(_s_,MIN(FIND({1,2,3,4,5,6,7,8,9,0},_s_&1234567890))-1)

sを数字とそれ以外で分割し、idx番目のグループを取り出す。(数字)

ab123DEF45→ab,123,DEF,45→idx=1なら123、idx=2なら45

暫定版のため要最適化

=IFERROR(MID(_s_,
  (SMALL(INDEX(
    N(IF(MMULT(ISNUMBER(-MID("★"&_s_,ROW(INDIRECT("1:"&LEN("★"&_s_)))+{0,1},1))*1,{1;2})=2,1,10^17))
      *ROW(INDIRECT("1:"&LEN("★"&_s_))),
  0),_idx_)-1)+1,
  SMALL(INDEX(
    N(IF(MMULT(ISNUMBER(-MID("★"&_s_,ROW(INDIRECT("1:"&LEN("★"&_s_)))+{0,1},1))*1,{1;2})=1,1,10^17))
    *ROW(INDIRECT("1:"&LEN("★"&_s_))),
  0),_idx_)-1
  -(SMALL(INDEX(
    N(IF(MMULT(ISNUMBER(-MID("★"&_s_,ROW(INDIRECT("1:"&LEN("★"&_s_)))+{0,1},1))*1,{1;2})=2,1,10^17))
      *ROW(INDIRECT("1:"&LEN("★"&_s_))),
  0),_idx_)-1)),"")

sを数字とそれ以外で分割し、idx番目のグループを取り出す。(文字)

ab123DEF45→ab,123,DEF,45→idx=1ならab、idx=2ならDEF

暫定版のため要最適化

先頭が数字の場合、idx=1は空文字を返します。

=IFERROR(MID(_s_,
  IFERROR(SMALL(INDEX(
    N(IF(MMULT(ISNUMBER(-MID("★"&_s_,ROW(INDIRECT("1:"&LEN("★"&_s_)))+{0,1},1))*1,{1;2})=1,1,10^17))
    *ROW(INDIRECT("1:"&LEN("★"&_s_))),
  0),_idx_-1)-1+1,1),
  SMALL(INDEX(
      N(IF(MMULT(ISNUMBER(-MID("★"&_s_,ROW(INDIRECT("1:"&LEN("★"&_s_)))+{0,1},1))*1,{1;2})=2,1,10^17))
        *ROW(INDIRECT("1:"&LEN("★"&_s_))),
    0),_idx_)-1
  -IFERROR(SMALL(INDEX(
    N(IF(MMULT(ISNUMBER(-MID("★"&_s_,ROW(INDIRECT("1:"&LEN("★"&_s_)))+{0,1},1))*1,{1;2})=1,1,10^17))
    *ROW(INDIRECT("1:"&LEN("★"&_s_))),
  0),_idx_-1)-1,0)
),"")

昇順に並んだ数値配列の最大値を取得する

これ単体では使いませんが、本記事の複数箇所で#VALUE!を消すために使用しています。

10^10のところは適当ですが、最大で10^17あれば十分でしょう。

=LOOKUP(10^10,{1,6,13,20,30,#VALUE!,#VALUE!})

階和:1~nの和(n*(n+1)/2)を求める

※ネタです。=(n*(n+1)/2)で十分です。

=SUMPRODUCT(ROW(INDIRECT("A1:A"&_n_)))

階乗:1~nの積(FACT(n))を求める

※ネタです。=FACT(n)で十分です。

=PRODUCT(INDEX(
      ROW(INDIRECT("1:"&_n_)),
0))

fで区切られた1~nの連番を生成

※TEXTJOINが使えるのはOffice365またはExcel 2019以降です。

=TEXTJOIN(_f_,FALSE,INDEX(
  ROW(INDIRECT("1:"&_n_)),
0))

sの並び順を反転する(StrReverse)

VBAで言うStrReverse(_s_)に相当

※CONCATが使えるのはOffice365またはExcel 2019以降です。

=CONCAT(INDEX(MID(_s_,1+LEN(_s_)-ROW(INDIRECT("A1:A"&LEN(_s_))),1),))


TRIM関連

Excel標準のTRIM関数は「両端のスペースを削除し、各単語間のスペースは1つ残して削除」なので、VBAのように両端のみをTRIMすることは出来ない。

この式では上記StrReverseを応用している。

※CONCATが使えるのはOffice365またはExcel 2019以降です。

尚、TRIM関数は、半角スペース、全角スペース、

sの先頭だけTRIMする (LTrim)

=MID(_s_, FIND(LEFT(TRIM(_s_), 1), _s_), LEN(_s_))

sの末尾だけTRIMする (RTrim)

=LEFT(
  _s_,
  LEN(_s_)
    -FIND(RIGHT(TRIM(_s_), 1), CONCAT(INDEX(MID(_s_,1+LEN(_s_)
    -ROW(INDIRECT("1:"&LEN(_s_))),1),)))
    +1
)

sの両端だけTRIMする (TRIM)

=MID(
  _s_,
  FIND(LEFT(TRIM(_s_), 1), _s_),
  LEN(_s_)-FIND(RIGHT(TRIM(_s_), 1), CONCAT(INDEX(MID(_s_,1+LEN(_s_)
    -ROW(INDIRECT("1:"&LEN(_s_))),1),)))
    -FIND(LEFT(TRIM(_s_), 1), _s_)
    +2
)

スペースを完全に削除する

=SUBSTITUTE(SUBSTITUTE(_s_, " ", ""), " ", "")

NBSP(ノーブレークスペース)もまとめてTRIMする

WEBからコピペしたデータにはノーブレークスペースという特殊な文字が含まれています。

=TRIM(CLEAN(SUBSTITUTE(_s_,CHAR(160)," ")))

内部改行を削除する

Alt+Enterで入力できる内部改行は文字コード[LF]で、ASCIIコードは10です。置換ダイアログのCtrl+Jでも消せます。

=SUBSTITUTE(_s_,CHAR(10),"")

通常Excelでは入力されることは無いと思いますが、改行コードには[CR]という、ASCIIコード13があります。もし消えなくて困ったらこれも試してみましょう

=SUBSTITUTE(_s_,CHAR(13),"")


半角全角の分割

いまのところ、半角+全角または全角+半角の場合にしか対応していません。

たとえば「abcdeあいうdefえお」のような交互に出現する文字列では正常に動きません。

sから前半の半角文字列を取り出す

abcdeあいう→abcde

=LEFT(_s_,LENB(JIS(_s_))-LENB(_s_))

sから後半の全角文字列を取り出す

abcdeあいう→あいう

=RIGHT(_s_,LENB(_s_)-LEN(_s_))

sから前半の全角文字列を取り出す

あいうabcde→あいう

=LEFT(_s_,LENB(_s_)-LEN(_s_))

sから後半の半角文字列を取り出す

あいうabcde→abcde

=RIGHT(_s_,LENB(JIS(_s_))-LENB(_s_))


IPアドレス検証

IPアドレスとして正しいか検証する(入力規則用)

=IFERROR(
  PRODUCT(INDEX(N(
    MID(
      SUBSTITUTE(
        _s_,".",REPT(" ",100)
      ),
      ROW(INDIRECT("A1:A4"))*100-99,
      100
    )*1<2^8
  ),0))*LEFT(_s_,1)<>0,
FALSE)

IPアドレスとして正しいか検証する(シート用)

=IFERROR(PRODUCT(N(
  MID(
    SUBSTITUTE(
      _s_,".",REPT(" ",100)
    ),
  {1,101,201,301},
  100
  )*1<2^8
),LEFT(_s_))<>0, FALSE)

IPアドレスとして正しいか検証する(無制限)

=IFERROR(NOT(
  SUMPRODUCT(N(
    NOT(MID(
      SUBSTITUTE(
        _s_,".",REPT(" ",100)
      ),
      ROW(INDIRECT("A1:A4"))*100-99,
      100
    )*1<2^8)
  ))+(INT(LEFT(_s_,1))=0)
),FALSE)

注意事項

いわゆるCSE式(Ctrl+Shift+Enter)による配列数式は使っていませんが、CSE式を使ったほうがシンプルに書けるものもあります。

私の独断と気分で適当に改行してインデント付けていますが、気に入らない方はエクセルの置換でCtrl+Jを使えば消せます。

エクセルから記事に転写する過程でミスがあるかもしれません。

基本的にはExcel2007互換です。

いくつかはExcel2019からの新関数により簡略化できるものもあります。

汎用性、安全性を最優先しており、あえて複雑な式になっているものもあります。

式が間違っていて読者様に何が起こったとしても、私は責任を取れません。

でも間違い報告は大歓迎です。

これも追加してほしい!というのがあれば、報告いただければ気が向いたら追加します。


間違いが御座いましたらTwitterから連絡いただけると助かります。