Excel～LEN関数で文字列の文字数を取得しよう～エクセル
エクセルで文字列の文字数を表示するLEN関数について説明します。
- LEN関数の書式はLEN(文字列)になります。
- 文字列は文字数をカウントしたい文字列を指定します。
下の画像の例でセルB2にセルA2の文字列の文字数を表示したい場合はセルB2に=LEN(A2)と入力しEnterを押します。
文字列はセルA2の文字列の文字数をカウントしたいのでA2になります。
つまりセルB2に=LEN(A2)と入力しEnterを押すと、セルA2の文字列"EXCEL"の文字数は5が表示されます。
スペースはカウントされる
LEN関数はスペースを1文字としてカウントします。
下の画像の例でセルB2にセルA2の文字列の文字数を表示したい場合はセルB2に=LEN(A2)と入力しEnterを押します。
文字列はセルA2の文字列の文字数をカウントしたいのでA2になります。
つまりセルB2に=LEN(A2)と入力しEnterを押すと、セルA2の文字列"Microsoft Office"の文字数はスペースを含めて16文字なので16が表示されます。
IF関数との組み合わせ
LEN関数とIF関数を組み合わせれば文字列の文字数を条件にして表示を変えられます。
下の画像の例でセルB5にセルA2の文字数制限を参照して、制限以下なら"OK"と表示し、制限を超えたら"オーバー"と表示したい場合はセルB5に=IF(LEN(A5)<=$A$2,"OK","オーバー")と入力しEnterを押します。オートフィルした時に参照がズレないように絶対参照$を使っています。
IF関数の書式はIF(論理式,[値が真の場合],[値が偽の場合])になります。
IF関数の論理式にLEN(A5)<=$A$2を指定すればセルA5の文字列の文字数がセルA2の数値以下の場合は論理式を満たすので、IF関数の[値が真の場合]が表示されます。セルA5の文字列の文字数がセルA2の数値を超えた場合は論理式を満たさないので、IF関数の[値が偽の場合]が表示されます。
IF関数の[値が真の場合]には論理式を満たした場合に"OK"と表示したいので"OK"になります。
IF関数の[値が偽の場合]には論理式を満たさなかった場合に"オーバー"と表示したいので"オーバー"になります。
つまりセルB5に=IF(LEN(A5)<=$A$2,"OK","オーバー")と入力しEnterを押すと、セルA5の文字列"HELLO"は5文字なのでセルA2の文字数制限の値5以下で論理式を満たすので"OK"と表示されます。
セルB5からセルB6までオートフィルするとセルA6の文字列"HELLO WORLD"はスペースを含めて11文字でセルA2の文字数制限の値5を超えているので論理式を満たさずに"オーバー"が表示されます。
SUM関数との組み合わせ
LEN関数とSUM関数を組み合わせれば別セルの文字列の文字数を合計することができます。
下の画像の例でセルC2にセルA2とセルB2の文字列の文字数の合計を表示したい場合はセルC2に=SUM(LEN(A2),LEN(B2))と入力しEnterを押します。
SUM関数の書式はSUM(数値1, [数値 2], ...)になり、指定した全ての数値を合計します。
SUM関数の数値1はセルA2の文字列の文字数を指定したいのでA2になります。
SUM関数の[数値 2]はセルB2の文字列の文字数を指定したいのでB2になります。
つまりセルC2に=SUM(LEN(A2),LEN(B2))と入力しEnterを押すと、セルA2の文字列"Microsoft"の文字数9と、セルB2の文字列"Office"の文字数6の合計の15が表示されます。
セル内の単語の数をカウントする
LEN関数とSUBSTITUTE関数を組み合わせればセル内の単語の数をカウントできます。
下の画像の例でセルB2にセルA2の単語数を表示したい場合はセルB2に=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1と入力しEnterを押します。
まずLEN(A2)はセルA2の住所の半角スペースを含んだ文字数を表します。
次にLEN(SUBSTITUTE(A2," ",""))について説明します。LEN関数の文字列にSUBSTITUTE(A2," ","")が入っています。SUBSTITUTE関数はSUBSTITUTE(文字列, 検索文字列, 置換文字列)で指定した文字列の中の検索文字列を置換文字列に置き換えます。SUBSTITUTE関数の文字列にセルA2、検索文字列に" "(半角スペース)、置換文字列に""(空白)を指定しているので、セルA2の住所の半角スペースを全て削除することを表します。つまりLEN(SUBSTITUTE(A2," ",""))はセルA2の住所から半角スペースを全て削除した文字列の文字数を表します。
つまりLEN(A2)-LEN(SUBSTITUTE(A2," ",""))はセルA2の住所の半角スペースを含んだ文字数から、セルA2の住所の半角スペースを全て削除した時の文字数を引いた値が求まります。これはセルA2の半角スペースの数になります。
最後に+1がついているので半角スペースの数に1を足すことで指定したセルの単語の数を求めています。
- 単語間の半角スペースが不特定多数の場合は上記の式では正確な単語数が求められないので以下の数式を使用してください。
- =LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1
- 半角全角スペース混合の場合は上記の式では正確な単語数が求められないので以下の数式を使用してください。
- =LEN(TRIM(A2))-LEN(SUBSTITUTE(SUBSTITUTE(A2," ","")," ",""))+1
- 空白の場合は1となってしまうので空白の可能性がある場合は以下の数式を使用してください。
- =IF(LEN(TRIM(A4))=0,0,LEN(TRIM(A4))-LEN(SUBSTITUTE(SUBSTITUTE(A4," ","")," ",""))+1)
RIGHT関数、FIND関数との組み合わせ
下の画像の例で商品IDから数字部分を抽出したい時にアルファベットと数字の文字数が決まっていない場合はRIGHT関数、LEN関数、FIND関数を組み合わせれば抽出できます。まずセルB2に=RIGHT(A2,LEN(A2)-FIND("-",A2))と入力しEnterを押すとセルB2にセルA2の商品IDの数字部分678が表示されます。
RIGHT関数の書式はRIGHT(文字列, [文字数])になり、文字列の末尾(右端)から指定された数の文字を返します。
RIGHT関数の文字列はセルA2に入力された商品IDを指定するのでA2になります。
RIGHT関数の[文字数]にはLEN(A2)-FIND("-",A2)が入力されています。LEN(A2)はセルA2の商品IDの文字数の8を意味します。FIND関数はFIND("探したい文字",文字列)で探したい文字が文字列の左から何番目か返します。探したい文字に代入さているのが-(ハイフン)で文字列に代入さているのがA2になるのでセルA2に入力された商品IDの中で-(ハイフン)が左から何番目にあるか返すので5を意味します。つまり商品IDの文字数8から-(ハイフン)の位置5を引くと数字部分の文字数3が求まり、これがRIGHT関数の[文字数]として使われます。
つまりセルB2に=RIGHT(A2,LEN(A2)-FIND("-",A2))と入力しEnterを押すとセルA2の商品IDから数字部分がセルB2に表示されます。
セルB2からセルB5までオートフィルすればA列の商品IDの数字部分がB列に表示されます。
ワークシートのシート名の表示
RIGHT関数、CELL関数、LEN関数,FIND関数を組み合わせるとワークシートのシート名を表示できます。下記リンクを参照してください。