えくせるちゅんちゅん

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

MENU

Excel関数で文字列処理を行う数式集

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

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

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


はじめに

エクセルでは数値・日付・文字列を様々な書式に変換しなければならない場面が多々あります。

そこで、私がこれまでに作成した文字列変換を行うための数式を、ここにまとめていくことにしました。


特に記載がないものは、Excel2007以上対応、配列数式不使用です。

中には配列数式を使ったほうがシンプルに書けるものもありますが、配列数式にするとCSEで確定しなければならず、事故の元となるため極力使わない方法を採用しています。(CSE=Ctrl+Shift+Enter)

私の独断と気分で適当に改行してインデント付けていますが、置換の検索対象にCtrl+Jキーで内部改行を入力すれば消せます。(※セルのAlt+Enterと同一の文字=LF

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

※もしOffice365であれば、スピル機能により CSEINDEX(式, 0) も不要です。 ※もしOffice365であれば、新関数を使ったほうが遥かに簡単な実装が可能なものもあります。


本来セル参照となる部分は、次のような名称に置き換えています。

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

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

名前定義でセルにこれらの名前付けると検証が楽になります。

あるいは_s_などの名称をA1にでも置換してください。

Office365なら、LET関数を使うことで検証しても良いでしょう。


自力で数式を解読したい人へ

本ページでは詳しい解説を特に行っていません。

下記の記事を読んで頂くと、解読の参考になるかと思います。

www.excel-chunchun.com

www.excel-chunchun.com

www.excel-chunchun.com

実際に動かしてみるのが一番だと思います。



文字列処理(文字数で判断)

文字列_s_に対して、任意の文字数_n_で抽出または削除するような処理

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

=LEFT(_s_,_n_)
項目
_s_ 12345
_n_ 3
結果 123

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

=RIGHT(_s_,_n_)
項目
_s_ 12345
_n_ 3
結果 345

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

=REPLACE(_s_,1,_n_,"")
項目
_s_ 12345
_n_ 3
結果 45

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

=LEFT(_s_,LEN(_s_)-_n_)
項目
_s_ 12345
_n_ 3
結果 12


文字列処理(区切り文字で判断)

文字列_s_に対して、任意の文字列_f_で抽出または削除するような処理

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

=COUNTIF(_s_,"*"&_f_&"*")>0
項目
_s_ 10.168,1.25
_f_ ,
結果 TRUE

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

=LEFT(_s_,FIND(_f_,_s_))
項目
_s_ 11,22,33,44,55
_f_ ,
結果 11,

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

=LEFT(_s_,FIND(_f_,_s_&_f_)-1)
項目
_s_ 11,22,33,44,55
_f_ ,
結果 11

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

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

=LEFT(_s_,FIND(_f_,_s_,FIND(_f_,_s_)+1)-1)
項目
_s_ 11,22,33,44,55
_f_ ,
結果 11,22

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

=REPLACE(_s_,1,FIND(_f_,_s_)+(LEN(_f_)-1),"")
=RIGHT(_s_,LEN(_s_)-FIND(_f_,_s_)-(LEN(_f_)-1))
項目
_s_ 11,22,33,44,55
_f_ ,
結果 22,33,44,55

sの末尾から最後のfの後まで取り出す(CSVの最後の要素)

=TRIM(RIGHT(SUBSTITUTE(_s_,_f_,REPT(" ",LEN(_s_))),LEN(_s_)))
項目
_s_ 11,22,33,44,55
_f_ ,
結果 55

使用例

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

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_ 1,22,333,44
_f_ ,
結果 9

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

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

使用例

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

  • ファイルパスからフォルダパスを取得
  • ファイル名から拡張子以外の名前を取得
項目
_s_ 1,22,333,44
_f_ ,
結果 1,22,333

sからHTMLやXMLのタグを取り出す

=REPLACE(LEFT(_s_,FIND(">",_s_)-1),1,FIND("<",_s_),"")

sからHTMLやXMLのタグに挟まれた値を取り出す

=REPLACE(LEFT(_s_,FIND("</",_s_)-1),1,FIND(">",_s_),"")


文字列処理(要素分割)

主に文字列_s_、区切り文字_f_、要素番号_idx_を使って任意の文字列を取り出すような処理

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

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

=(LEN(_s_)-LEN(SUBSTITUTE(_s_,_f_,"")))/LEN(_f_)
項目
_s_ 11,22,33,44,55
_f_ ,
結果 4

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

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

=(LEN(_s_)-LEN(SUBSTITUTE(_s_,_f_,"")))/LEN(_f_)+1
項目
_s_ 11,22,33,44,55
_f_ ,
結果 5

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

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

=FIND("★",SUBSTITUTE(_s_,_f_,"★",_idx_))
項目
_s_ 1,22,333,44
_f_ ,
_idx_ 2
結果 5

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

=LEFT(_s_,FIND("★",SUBSTITUTE(_s_&_f_,_f_,"★",_idx_))-1)
項目
_s_ 1,22,333,44
_f_ ,
_idx_ 2
結果 1,22

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_ 1,22,333,44
_f_ ,
_idx_ 2
結果 333,44

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

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

配列を作った後にINDEXで要素を選択しているので、配列のまま他の式に応用できる。

=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_ 1,22,333,4444,55555,6
_f_ ,
_idx_ 4
結果 4444

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に含まれる数字の個数をカウント

=SUMPRODUCT(N(ISNUMBER(-MID(_s_,ROW(INDIRECT("A1:A"&LEN(_s_))),1))))
_s_ 結果
123abc456 6
abcdef 0
123abc456 6
abc123def 3
123 3

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

abc123→1、a→""

=MID(_s_,MIN(FIND({0,1,2,3,4,5,6,7,8,9},_s_&"0123456789")),1)
項目
_s_ abc123def
結果 1
項目
_s_ abcdef
結果 ``
項目
_s_ 123abc456
結果 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_ 結果
123 123
123abc456 123abc456
abcdef #N/A
abc123def 123def

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_ 結果
123 123
123abc456 123
abcdef ``
abc123def abc123

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

abc123def → 123def → 123

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

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

10^17を超えるような数字は取り出せないが、実数値も取り出すことができる。

_s_ 結果
123 123
123abc456 123
abcdef #N/A
abc123def 123

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_ 結果
123 ``
123abc456 ``
abcdef ``
abc123def 123

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

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

=LEFT(_s_,MIN(FIND({1,2,3,4,5,6,7,8,9,0},_s_&1234567890))-1)
_s_ 結果
123 ``
123abc456 ``
abcdef abcdef
abc123def abc

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

※暫定版のため要最適化

=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_ aaa111bb22c3dddd44eee555
_idx_ 0 ``
_idx_ 1 111
_idx_ 2 22
_idx_ 3 3
_idx_ 4 44
_idx_ 5 555
_idx_ 6 ``

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

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

※暫定版のため要最適化

=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)
),"")
項目 結果
_s_ aaa111bb22c3dddd44eee555
_idx_ 0 ``
_idx_ 1 aaa
_idx_ 2 bb
_idx_ 3 c
_idx_ 4 dddd
_idx_ 5 eee
_idx_ 6 ``

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

sの数字部分だけを取り出す

=CONCAT(INDEX(
  MID(_s_,
    ROW(INDIRECT("1:"&LEN(_s_))),
    ISNUMBER(-MID(_s_,ROW(INDIRECT("1:"&LEN(_s_))),1))
  ),
,0))


数値・計算

文字列の数値化

文字列処理だけでは「文字列」型のデータとなるため、SUM関数などの集計対象になりません。

そこで、適当な計算をさせることで数値データor日付データシリアル値にすることができます。

説明
文字列を数値化・日付化する(VALUE) =VALUE(_s_)
文字列を数値化・日付化する(+0) =_s_+0
文字列を数値化・日付化する(*1) =_s_*1
文字列を数値化・日付化する(^0) =_s_^0
文字列を数値化・日付化する(--) =--_s_


文字列処理によって、何らかの数学的な計算を行う式

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

=LOOKUP(10^17,LEFT(_s_,ROW(INDIRECT("1:"&LEN(_s_))))*1)
_s_ 結果
123 123
123abc456 123
abcdef #N/A
abc123def #N/A

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_ 1,2,3,4,5
_f_ ,
結果 15

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_ a,1,b,2,c,3
_f_ ,
結果 6

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
  )
)

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

これ単体では使いませんが、本記事の複数箇所で#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))
項目
_n_ 3
結果 1,2,3


文字列処理(文字列加工)

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

VBAで言うStrReverse(_s_)に相当

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

=CONCAT(INDEX(MID(_s_,1+LEN(_s_)-ROW(INDIRECT("A1:A"&LEN(_s_))),1),))
列名 項目
s _s_ 1,22,333,4444,55555,6
結果 結果 6,55555,4444,333,22,1

英字のA~Zと数字の0~9を交互に連結

=TEXTJOIN(INDEX(
  ROW(INDIRECT("1:10"))-1,0),
  TRUE,
  INDEX(CHAR(CODE("A")+ROW(INDIRECT("1:26"))-1),
0))
結果
A0B1C2D3E4F5G6H7I8J9K0L1M2N3O4P5Q6R7S8T9U0V1W2X3Y4Z

sをカタカナやひらがなに変換(ふりがな)

「ふりがな」機能を活用した例です。

  • 「ふりがなの設定Alt→H→G→T」で「ひらがな」「全角カタカナ」「半角カタカナ」が選択できます。
  • _s_は文字列(値)ではなく、セル参照である必要があります。
  • 数式の入ったセルを参照した場合、「ふりがな」は空欄となり使えません。
=PHONETIC(_s_)
項目
_s_ あいうえお
結果 アイウエオ

ひらがなをカタカナに変換

=CONCAT(INDEX(
  UNICHAR(
     UNICODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))+
       96*BITAND(
         UNICODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>=12353,
         UNICODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<=12438
      )),
0))

カタカナをひらがなに変換

=CONCAT(INDEX(
  UNICHAR(
     UNICODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))-
       96*BITAND(
         UNICODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>=12449,
         UNICODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<=12534
      )),
0))

www.excel-chunchun.com

sをUNICODEの16進数f区切り文字列に変換

=TEXTJOIN(",",FALSE,INDEX(
  RIGHT("0000"&DEC2HEX(UNICODE(
    MID(_s_,ROW(INDIRECT("1:"&LEN(_s_))),1)
  )),4),
0))
A B
1 あいうえお 3042,3044,3046,3048,304A
2 おはようございます! 304A,306F,3088,3046,3054,3056,3044,307E,3059,FF01
3 こんばんわ 3053,3093,3070,3093,308F
4 ABCDE 0041,0042,0043,0044,0045
5 12345 0031,0032,0033,0034,0035

sの文字と文字の間に区切り文字fを挿入(数字限定)

※TEXT関数の仕様に依存するため、使えない区切り文字があります。

=TEXT(_s_,REPT("0"&_f_,LEN(_s_)-1)&"0")
項目
_s_ 123456
_f_ -
結果 1-2-3-4-5-6

sの文字と文字の間に区切り文字fを挿入

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

=TEXTJOIN(_f_,FALSE,INDEX(
  MID(_s_,ROW(INDIRECT("1:&LEN(_s_))),1),
0))
項目
_s_ abcde
_f_ -
結果 a-b-c-d-e


TRIM関連

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

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

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

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

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

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

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

=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)

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

=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_))


書式設定変換(TEXT関数)

TEXT関数による様々な書式変換

TEXT関数を使うことで、書式設定を使った数値→文字列変換が可能です。

文字列型の数字は数値とみなして処理してくれるので、文字列変換に応用することもできます。

=TEXT(値,表示形式)
タイトル 書式 入力例 出力例
数値を3桁毎のコンマ区切りへ変換する(1234567→1,234,567) ###,### 1234567 1,234,567
数値を携帯版で番号書式へ変換する(09012345678→090-1234-5678) 000-0000-0000 09012345678 090-1234-5678
数値をスラッシュ区切りの年月日へ変換する(20180405→2018/04/05) 0000!/00!/00 20180405 2018/04/05
数値をコロン区切りの時分秒へ変換する(100534→10:05:34) 00!:00!:00 100534 10:05:34
数値を6桁の固定長数字へ変換する(123→000123) 000000 123 000123
数値を千円へ変換する(12345→12千円) 0,千円 12345 12千円
数値を千円へ変換する(123456789→123百万円) 0,,百万円 123456789 123百万円
日付を8桁数字に変換する(2018/4/5→20180405) yyyymmdd 2018/4/5 20180405
日時を14桁数字に変換する(2018/4/5 10:05:34→20180405100534) yyyymmddhhmmss 2018/4/5 10:05:34 20180405100534
日付を日付と曜日に変換する(2018/4/5→2018/04/05 (木)) yyyy/mm/dd (aaa) 2018/4/5 2018/04/05 (木)

また、2018/04/0510:05:34という形式の文字列をVALUE関数で処理すればシリアル値に変換できます。

=VALUE(TEXT(値,表示形式))

TEXT関数についてGoogle検索をすると、他にも様々な利用例が見つかります。

数値を+正の数、±0、-負の数表記にする(10→+10、0→±0、-10→-10)

=TEXT(_s_,"""+""??,??0;""-""??,??0;""±""????0")
入力例 出力例
12345 +12,345
-12345 -12,345
0 ± 0

コミュファ光の通話時間を時刻に変換する

=TEXT(0+SUBSTITUTE(_s_,":",""),"00!:00!:00")
入力例 出力例
8 00:00:08
1:17 00:01:17
10:35 00:10:35
1:05:03 01:05:03

上記では文字列となりますが、シリアル値にしたい場合は仕上げにTIMEVALUE関数VALUE関数を使用します。

=VALUE(TEXT(0+SUBSTITUTE(_s_,":",""),"00!:00!:00"))


IPアドレス検証

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

※セルの入力規則のユーザー定義にて使用。_s_は検証したいセルアドレスに置き換える。

=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)
TRUE / 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)


おわりに

式が間違っていて、読者様に何が起こったとしても私は責任を取れませんが、間違い報告を頂ければ修正致します。

こういう文字列処理もしたい!と連絡いただければ、気が向いたら追加します。


何か御座いましたらコメント欄、またはTwitterからどうぞ♪

それではまた来週♪ ちゅんちゅん(・8・)

プライバシーポリシー