エクセル関数で文字列を自由自在に変換するための数式を大公開します。(随時更新)
載せて欲しい式や、バグ等があればコソッと教えてください。
- はじめに
- 自力で数式を解読したい人へ
- 文字列処理(文字数で判断)
- 文字列処理(区切り文字で判断)
- 文字列処理(要素分割)
- 文字列処理(数字)
- 数値・計算
- 文字列処理(文字列加工)
- TRIM関連
- 半角全角の分割
- 書式設定変換(TEXT関数)
- IPアドレス検証
- おわりに
はじめに
エクセルでは数値・日付・文字列を様々な書式に変換しなければならない場面が多々あります。
そこで、私がこれまでに作成した文字列変換を行うための数式を、ここにまとめていくことにしました。
特に記載がないものは、Excel2007以上対応、配列数式不使用です。
中には配列数式を使ったほうがシンプルに書けるものもありますが、配列数式にするとCSEで確定しなければならず、事故の元となるため極力使わない方法を採用しています。(CSE=Ctrl+Shift+Enter)
私の独断と気分で適当に改行してインデント付けていますが、置換の検索対象にCtrl+J
キーで内部改行を入力すれば消せます。(※セルのAlt+Enter
と同一の文字=LF
)
汎用性、安全性を最優先しており、あえて複雑な式になっているものもあります。
※もしOffice365であれば、スピル機能により CSE も INDEX(式, 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関数を使うことで検証しても良いでしょう。
自力で数式を解読したい人へ
本ページでは詳しい解説を特に行っていません。
下記の記事を読んで頂くと、解読の参考になるかと思います。
実際に動かしてみるのが一番だと思います。
文字列処理(文字数で判断)
文字列_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))
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/05
や10: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・)