オーロラさんの勉強帳

IT企業勤務。データベース、Excel、Excel VBA、ネットワーク、LinuxなどIT関連のことを主に書いていきます。少しでもお役に立てたら幸いです。

【Excel】LEN・LENB関数を使って全角・半角をチェックする方法をわかりやすく説明 ~情シスあるある。Excelの申請書・依頼書で使える知識~

情シスで働いていると、Excelの申請書・依頼書でユーザから依頼がくるケースは多いと思います。また、登録・更新するデータが入力されたExcelやCSVファイルが添付されて依頼がくるようなこともあります。

そのExcelを基にシステムの管理者画面やデータベースに直接SQLで登録や更新を行うのですが、ユーザから送付されたExcelのデータに不備が多いこと多いこと。
全角、半角のルールが守られていなかったり、必須項目の入力が漏れていたり、文字数が超過していたり。

今回はそんなときに使える全角・半角を確認する方法をわかりやすく説明いたします。


LEN関数、LENB関数の説明

全角、半角の確認を行う際に使用するLEN関数、LENB関数について説明します。

LEN関数

LEN関数は引数に指定した文字列の文字数を返します。半角文字(1バイト)、全角文字(2バイト)も関係なく、1文字は1として数えます。


■LEN関数の書式
=LEN(文字列)

LENB関数

LENB関数は引数に指定した文字列のバイト数を返します。半角文字は1バイト、全角文字は2バイトです。


■LENB関数の書式
=LEN(B文字列)

LEN関数・LENB関数の使用例

文字列が入力している特定の1セルをLENB関数の引数として指定した使用例

引数にセル範囲を指定する『#SPILL!』とエラーになります。

#SPILL!エラー


LEN関数、LENB関数の引数として、文字列を直接指定した使用例


LEN・LENB関数を使って入力された文字列が全角のみ、半角のみ、全角・半角混在かを確認する方法

LEN・LENB関数で全角のみ、半角のみ、全角半角混在を判断する考え方

<全角のみ、半角のみ、全角半角混在を判断する方法>
■全角のみの場合
 文字数×2=バイト数になります。
 つまり、『LEN関数の結果×2 = LENB関数の結果』となります。
 
■半角のみの場合
 文字数=バイト数になります。
 つまり、『LEN関数の結果 = LENB関数の結果』となります。

■全角半角混在の場合
 全角のみの場合にも、半角のみの場合にも合致しない場合は、全角半角混在のデータとなります。

Excel 全角・半角の判断方法
Excel 全角・半角の判断方法

申請書や依頼書で文字列が全角のみ、半角のみ、全角・半角混在かをチェックしてみる

以下の申請書で全角、半角のチェックをしてみましょう。
B列(社員番号)は半角、C列(社員氏名)は全角、D列(フリガナ)は半角で入力する必要があります。

LEN・LENB関数を使って全角・半角をチェックする

単純にTRUE、FALSEで申請書の規則通りに全角のみ、半角のみで入力されているかを確認しています。

■B列、D列の半角チェック
『=LEN(B5)=LENB(B5)』、『=LEN(D5)=LENB(D5)』のように、全角のみなら「TRUE」、全角以外の文字があるなら「FALSE」を返します。

■C列の全角チェック
『=LEN(C5)*2=LENB(C5)』のように、半角のみなら「TRUE」、半角以外の文字があるなら「FALSE」を返します。


条件式書式で申請書の規則に違反するデータにわかりやすい背景色を設定する

条件付き書式で、申請書の規則に違反するデータに背景色を設定する方法を記載します。
以下の例は、B列について半角の指定があるので、全角の文字が含まれる場合に背景色を黄色に設定します。

1.「ホーム」タブ、「条件付き書式」、「新しいルール」を順次選択します。

2.「数式を使用して、書式設定するセルを決定」を選択、「次の数式を満たす場合に値を書式設定」欄に『=LEN(B5)<>LENB(B5)』と入力、「書式」を選択します。
※=LEN(B5)<>LENB(B5)は、半角以外の文字が含まれた場合に書式を設定する条件となります。
※C列で全角以外の文字が含まれるセルを強調させる場合は、セル「C5」に『=LEN(C5)*2<>LENB(C5)』を指定します。

3.「塗りつぶし」タブを選択、「背景色」に黄色を選択、「OK」を選択します。

4.「OK」を選択します。

5.条件式書式を設定したセル「B4」をコピーし、条件付き書式を設定したいセル範囲を複数選択し(画像の場合はセルB5:B8)、右クリック、貼り付けオプションの「書式設定」を選択。

6.半角以外の文字があるセル(全角文字が含まれるセル)の背景色を黄色で強調することができました。


事前に申請書・依頼書のExcelに、全角半角・必須・文字数などを条件付き書式や入力規則で依頼者に気づかせれるように作成しておくのが、効率のいいやり方だと思います。
ただ、それでも不備のある状態で依頼されてしまうのが、情シスあるあるといったところでしょうか。

以上、お読みいただきありがとうございました。