Excelにある文字列操作関数は何がありますか?本記事では、Excelで常用な文字列操作関数を解説します。参考価値があるため、必要があれば是非ご覧ください。
目次
文字列の位置を検索する
一連の文字列内指定された文字列の位置を精確に検索することはExcel関数の使用に対して重要なスキルであり、特に文字列の検索や置換などの文字列操作では、位置を精確に検索することは不可欠です。
FIND関数
説明
FIND関数は、指定した検索文字列を他の文字列の中で検索し、その検索文字列が最初に現れる位置を左端から数えた位置を返します。
書式
FIND(検索文字列, 対照, 開始位置)
引数
- 検索文字列:検索する文字列を指定します。(必須)
- 対照:検索文字列を含む文字列を指定します。(必須)
- 開始位置:検索を開始する位置を指定します。対象の先頭文字から検索を開始する時は1を指定します。開始位置を省略すると、1を指定したと見なされます。(オプショナル)
例
数式 | 説明 |
=FIND(“M”,A2) | セルA2にある最初の「M」文字の位置を検索します |
=FIND(“m”,A2) | セルA2にある最初の「m」文字の位置を検索します |
=FIND(“M”,A2,3) | セルA2の3番目の文字から最初の「M」の位置を検索します |
文字列の抽出と結合
文字列の抽出
常用な抽出関数には、LEFT関数、RIGHT関数、およびMID関数が含まれます。
LEFT関数
説明
LEFT関数、文字列の先頭から指定された数の文字を返します。
書式
LEFT(文字列, 文字数)
引数
- 文字数:取り出す文字数を指定します。(オプショナル)
- 文字数は0または正数に限ります。
- 文字数は指定された文字列の文字数より大きい場合、LEFT関数はすべての文字列を返します。
- 文字数を省略すると、1を指定したと見なされます。
例
数式 | 説明 |
=LEFT(A2,4) | セルA2にある最初の4文字 |
=LEFT(A3) | セルA3にある最初の文字 |
RIGHT関数
説明
RIGHT関数の使い方はLEFT関数と似ています。右側から文字列を取り出し、つまり最後のN個文字を取り出します。例えば、次の例の数式は「HOME」を結果として返します。
=RIGHT("This is my HOME",4)
MID関数
説明
取り出す文字は文字列の真ん中にある場合にはMID関数を使用できます。
MID関数は、文字列の指定した位置型、指定された数の文字を返します。
書式
MID(文字列,開始位置, 文字数)
引数
- 文字列:検索の対象となる文字を含む文字列を指定します。(必須)
- 開始位置:抜き出したい文字列の先頭文字の位置を指定します。(必須) 最初の文字の文字数は1となり、2番目の文字の文字数2となり、…
- 文字数:文字列から抜き出す文字の数を指定します。
例
数式 | 説明 |
=MID(A2,1,5) | セルA2にある文字列の最初の文字から始めて、5文字を返します。 |
LEN関数
説明
LEN関数、文字列の長さ(文字数)を返します。
書式
LEN(文字列)
引数
文字列 | 長さを求めたい文字列を指定します。(必須)空白も文字数に合算します。 |
文字列の結合
文字列を結合して連結するため、常用なやり方は、「&」を使用することです。
例えば、次の数式の結果は文字列「Hello!TOKYO!」です。
="Hello!" & "TOKYO!"
文字列の置き換え
SUBSTITUTE 関数
説明
文字列の検索文字列を置換文字列に置き換えます。 指定した文字列に文字を置き換える場合、SUBSTITUTE関数を使用してください。
注意点:SUBSTITUTE関数は、置き換える文字列を指定する必要がありますが、文字列内の具体的な位置を省略できます。
書式
SUBSTITUTE(文字列, 検索文字列, 置換文字列, 置換対象)
引数
- 文字列:置き換える文字を含む文字列、または目的の文字列が入力されたセル参照を指定します。(必須)
- 検索文字列:置き換え前の文字列を指定します。(必須)
- 置換文字列:置き換え後の文字列を指定します。(必須)
- 置換対象:文字列に含まれるどの検索文字列を置換文字列に置き換えるかを指定します。置換対象が指定されている場合、要件を満たす検索文字列のみ置き換えられます。指定しなければ、文字列内すべての検索文字列は置換文字列に変更されます。
例
ローデータ:「HELLO!TOKYO!」、セルA2に記述されます。
SUBSTITUTE(A2,"TOKYO","OSAKA")
結果
HELLO!OSAKA!
REPLACE関数
説明
指定された文字数によって、REPLACEは一部の文字列を他の文字列に置き換えます。一連の文字列で指定位置の文字列を置き換える場合は、REPLACE関数を使用してください。
注意点:REPLACE関数は、わざわざ置き換え目的の文字列を指定する必要がなく、ただ位置を指定すれば結構です。
書式
REPLACE(文字列, 開始位置,文字数, 置換文字列)
引数
- 文字列:置き換えたい文字列が含まれる文字列を指定します。(必須)
- 開始位置:[置換文字列]で置き換えたい文字列の先頭文字の位置を指定します。(必須)
- 文字数:置換文字列]で置き換えたい文字列の文字数を指定します。(必須)
- 置換文字列:置き換え後の文字列を指定します。(必須)
例
元の文字列:「HELLO!TOKYO!」、セルA2に記述されます。
数式 | 解説 | 結果 |
=REPLACE(E7,FIND(“!”,E7)+1,4 | “OSAKA!HAKONE!YOKOHAMA!”) 最初の“!”後の文字から、「OSAKA!HAKONE!YOKOHAMA!」を置換します。 | HELLO!OSAKA!HAKONE!YOKOHAMA! |
文字列の挿入
説明
REPLACE関数の3番目の引数が0の場合、文字列が挿入できます。この時、文字列が置き換えできません。
例
元の文字列:「HELLO!TOKYO!」、数式で内容を「HELLO!TOKYO!OSAKA!HAKONE!YOKOHAMA!」に変更します。
=REPLACE(E7,FIND("!",E7)+1,0,"OSAKA!HAKONE!YOKOHAMA!")
文字列の削除
TRIM 関数
説明
TRIM関数は、文字列の最初と最後にある空白または文字列にある連続の空白を削除でき、英語の文字列の訂正処理に適しています。
注意点:TRIM関数は、冗長な空白を削除するために使用でき、文字列の分解と抽出に適しています。
書式
TRIM(文字列)
CLEAN 関数
説明
文字列内印刷できない文字をすべて削除します。 他のアプリケーションからインポートされた文字列にCLEAN関数を使用すると、今のOSに含まれる印刷できない文字が削除されます。例えば、CLEAN関数を使用して、通常、データファイルの最初と最後に表示され、印刷できない低レベルのコンピューターコードを削除できます。
書式
CLEAN(文字列)
神妙なTEXT関数
これはカスタム数値形式と非常に似ており、ほとんどの場合、両方どちらを使用してもいいです。 両方の相違点は以下となります。
1.カスタム数値形式では、アスタリスク(*)は、セルが補充されるまで文字を繰り返すことを意味します。TEXT関数ではこの機能が実現できないため、REPT機能を使用する必要があります。
2.カスタム数値形式では、8つの異なる色が提供され、これらの色はTEXT関数に使用できませんが、条件付き書式を使用してTEXT関数でこの機能を実現します。
3.カスタム数値形式は、ただ数値の表示形式を変更し、数値の本質は変更しません。これらの“変装”された数値は、加減乗除、合算または平均値を計算できます。TEXT関数はより優れています。“変装”されるだけでなく、本質も変更しやすいです。TEXT関数で処理された数値の中には、変質されたのため、合算に参与できなくなりました。
4.カスタム数値形式は、ローデータが記述されているセルで有効になり、TEXT関数を新しいセルに設定する必要があります。前者はより簡潔で後者はよりすばしこいです。
TEXT関数とカスタム数値形式の相違点を読んだ後、いくつかの例を見て、TEXT関数の機能をご覧ください。
次はエクセル文字列操作関数TEXTのチュートリアルとなります。
基本的な使い方
書式:
=TEXT(値,表示形式)
使い方
TEXT関数は、フォーマットコードによって数値にフォーマットを適用し、それによって数値の表示方法を変更できます。その中、引数①はフォーマットを変更したい値であり、文字列でも数値でも構いません。引数②は引数①を変更したい表示形式であり、実行原理はカスタム数値形式と同じです。 使い方は4つの部分に分け、英語のセミコロン(;)で分けます。
[正数の形式];[負数の形式];[0の形式];[文字列の形式]
具体的な使い方については、次に一つずつ説明します。
実例
ケース1:桁数を固定する
領収書番号を例として、通常番号は8桁ですが、システムで生成した番号はその前の0が省略される場合があります。この場合、0を自動的に入力するにはどうすればよいですか。次の数式を入力してください。
=TEXT(A4,"00000000")
解説:
引数②は「00000000」で、負数、0、文字列は省略されています。つまり、この形式はすべての数値に適用できますが、文字列には適用できません。 ここでの0はプレースホルダーです。つまり、桁に有効な値がある場合は有効な値が取得され、そうでない場合は桁に0を補充します。 セルA4を例として、1桁から6桁まで有効な値があるため、この部分は変更されません。最初の2桁には有効な値がなく、0で補充するため、550277は00550277になります。これはTEXT関数の作用です。
ケース2:生年月日を取り出し
一連の数値から生年月日を抽出し、日付形式で入力するにはどうすればよいですか? この問題には多くの解決策があり、TEXT+MID関数の組み合わせ数式はその1つです。
=TEXT(MID(A2,7,8),"0000-00-00")
解説:
MID(A2,7,8)は、18桁の番号から生年月日の8桁の文字列を抽出するために使用され、TEXT関数は、0000-00-00の形式で8桁の生年月日の文字列を表示します。注意点は、MID関数によって抽出される日付は実際の日付ではなく文字列であるため、「yyyy-mm-dd」という形式を使用して設定することはできないということです。 実際の日付の表示形式を変更する必要がある場合にのみ、y(年)、m(月)、d(日)の3文字を組み合わせて使用できます。
=TEXT(B2,"yyyy.mm.dd")
ケース3:簡単な条件判断
ある程度でTEXT関数をIF関数の拡張バージョンとして使用して、3つの条件を判断でき、条件を満たす値を指定された形式に変換することができます。地方企業の損益の判断を例によって、利益が正数の場合は黒字、負数は赤字、0は前期と同じと表示されます。次の数式を入力してください。
=TEXT(B2,"[>0] 黒字;[<0] 赤字;前期と同じ")
解説:
数値表現形式は2つの条件付き判断をサポートしています。[値1]表示形式1、[値2]表示形式2、表示形式3、引数② “[> 0] 黒字; [<0] 赤字; 前期と同じ”はこのやり方の実例です。列Bの数値に対して「値1」で判断を実行し、0より大きい場合は「黒字」を表示し、0以下の場合は「値2」の判断を実行し、0未満の場合は「赤字」を表示します。両方とも満たさない場合 、「前期と同じ」と表示されます。
ケース4:時間間隔の計算
働き時間や残業時間の計算方法は、HRが悩む問題です。しかし、TEXT関数を使えば、時間間隔とか、端数処理とか、すべて処理できます。
=TEXT(C2-B2,"[h]")
解説:
引数②の「[h]」は、値が1/24を単位として時間に変更し、整数桁のみ表示します。ここのhは時間です。同様に、分のmまたは秒のsと記述して、分と秒の間隔を計算することもできます。
=TEXT(C2-B2,"[m]")
=TEXT(C2-B2,"[s]")
ケース5:アラビア数字と日本語数字の変換
日本語数字とアラビア数字をどのように交換するかという質問は、多くの人が困っているでしょう?解決策は、TEXT関数の2番目の引数を「[DBnum1]」に設定すれば結構です。「[DBnum1]」はアラビア数字を日本語数字に変換できますが、整数に限りです。
数式1:
=TEXT(A2,"[DBnum1]")
数式2:
=MATCH(A2,TEXT(ROW($1:$9999),"[DBnum1]"),)
注意点
数式2を入力した後、数式をすべて選択して、(Ctrl+Shift+Enter)キーを押すことを忘れないでください。
解説
数式1は理解しやすく、「[DBnum1]」を使用してアラビア数字を日本語数字に変換します。数式2は理解しにくいです。1から9999までのアラビア数字を1つずつ日本語数字に変換して、一から九千九百九十九までの配列を表します。最後に、MATCH関数を使用して完全一致を実現します。列Aの日本語数字の位置によって、日本語数字に対応するアラビア語数字を変換します。 つまり、数式2の本質は数式1と同じであり、実際「[DBnum1]」によって実現され、すごい関数です。
コメントを残す