Excelで商品コード、郵便番号、社員番号などを扱うとき、セルの左側にある決まった文字だけを抜き出したい場面があります。
そのようなときに便利なのがLEFT関数です。
LEFT関数は文字列の先頭から指定した文字数を取り出せるため、データ整理、分類、別表への転記などで幅広く役立ちます。
LEFT関数の基本形は、LEFT(文字列,文字数)です。
文字列の左端から必要な文字数だけを抽出します。
数値が入力されているセルでも使用できますが、結果は基本的に文字列として返される点には注意が必要です。
この記事では、LEFT関数の基本構文から数値の扱い、エラーの対処法、ほかの文字列関数との組み合わせまで詳しく確認していきましょう。
LEFT関数で左から文字を取り出す方法
それではまずLEFT関数で左から文字を取り出す方法について解説していきます。
| A列 商品コード | B列 左から3文字 |
|---|---|
| TOK-1048 | TOK |
| OSA-2081 | OSA |
1行目には見出しがあり、データは2行目から入力されているものとして説明します。
LEFT関数の構文
LEFT関数の構文はLEFT(文字列,文字数)です。
文字列には抽出元となるセル、文字数には左から取得する文字の個数を指定します。
=LEFT(A2,3)
この数式をB2セルへ入力すると、A2セルのTOK-1048から左端の3文字であるTOKを取得できます。
文字数には全角文字も半角文字も、それぞれ1文字として数えられます。
たとえば「東京都新宿区」から3文字を取り出す場合、結果は「東京都」です。
【操作のポイント】文字列を直接入力する場合は、LEFT(“東京都新宿区”,3)のように文字列を二重引用符で囲みます。
セル参照で数式を入力する手順
続いてはセル参照でLEFT関数を入力する手順を確認していきます。
抽出結果を表示したいB2セルをクリックし、数式バーまたはセル内に=LEFT(A2,3)と入力します。
Enterキーを押すと、A2セルにある商品コードの先頭3文字がB2セルに表示されます。
セル参照を使う方法なら、元データを変更した場合にも抽出結果が自動で更新されます。
元のセルを見ながら入力したいときは、LEFT(と入力した後にA2セルをクリックすると参照先を指定できます。
関数の引数はカンマで区切ります。
数式入力中に表示される関数のヒントも確認すると、引数の順序を間違えにくくなります。
【操作のポイント】抽出先の列は元データの右側に作ると、数式の確認とコピーがしやすくなります。
オートフィルによる数式のコピー
続いてはオートフィルで数式を下方向へコピーする操作を確認していきます。
B2セルに正しい結果が表示されたら、セル右下にある小さな四角形のフィルハンドルへマウスを合わせます。
マウスポインターが黒い十字に変わった状態で下へドラッグすると、数式を連続してコピーできます。
隣接するA列にデータが途切れず入力されている場合は、フィルハンドルをダブルクリックする方法も便利です。
コピー後の数式は、A2がA3、A4のように行番号だけ自動で変わる相対参照になります。
大量のデータでも、最初の行に数式を作成してからオートフィルを使えば短時間で処理できます。
【操作のポイント】途中に空白行があるとダブルクリックでのコピー範囲が途中までになることがあります。
LEFT関数で数値と文字列を扱う仕組み
続いてはLEFT関数で数値と文字列を扱う仕組みを確認していきます。
| A列 元の値 | B列 数式 | C列 結果 |
|---|---|---|
| 123456 | =LEFT(A2,2) | 12 |
見た目は数値でも、LEFT関数で取り出した結果は文字列として扱われます。
数値を指定した場合の抽出結果
続いては数値が入力されたセルをLEFT関数で参照する場合を確認していきます。
A2セルに123456という数値が入っているとき、=LEFT(A2,2)の結果は12になります。
ただし、この12は計算用の数値ではなく、文字としての「12」です。
LEFT関数は値の表示を左から読み取り、文字列を返す関数です。
そのため、LEFT関数の結果へそのまま加算や平均の計算を行うと、意図した結果にならないケースがあります。
=VALUE(LEFT(A2,2))
抽出結果を数値として計算に使いたいときは、VALUE関数で囲む方法が分かりやすいでしょう。
【操作のポイント】後から集計する可能性がある番号は、抽出時点で文字列か数値かを決めておくと混乱を防げます。
先頭のゼロが消える理由
続いては先頭のゼロが消える理由を確認していきます。
郵便番号の0123456や管理番号の00125は、セルを数値形式で入力すると先頭のゼロが表示されません。
Excelは数値として扱う値の先頭にあるゼロを、桁を表す意味のないものとして省略するためです。
先頭ゼロを残したいコードは、入力前にセルの表示形式を文字列へ変更することが基本です。
すでに数値として入力した後なら、=TEXT(A2,”0000000″)のようにTEXT関数で必要な桁数へ整える方法があります。
=LEFT(TEXT(A2,”0000000″),3)
この式なら、数値の12345を7桁の0012345として整えたうえで、左から3文字の001を取り出せます。
【操作のポイント】郵便番号、社員番号、商品コードは四則演算をしないなら文字列として管理するのが安全です。
文字列として数値を判定する方法
続いては抽出結果が数値か文字列かを判定する方法を確認していきます。
=ISTEXT(B2)を入力すると、B2セルの内容が文字列ならTRUE、文字列以外ならFALSEが返されます。
LEFT関数の結果に対してISTEXT関数を使えば、通常はTRUEとなります。
表示が同じ12でも、数値の12と文字列の12では並べ替えや計算の動きが異なります。
文字列の数字を数値へ変換するには、VALUE関数のほかに、1を掛ける、貼り付け時に演算を選ぶといった方法もあります。
ただし、先頭ゼロを保持したいコードでは数値化を行わないよう注意しましょう。
【操作のポイント】コードの抽出結果を見た目だけで判断せず、必要ならISTEXT関数でデータ型を確認します。
LEFT関数の数式入力とオートフィル操作
続いてはLEFT関数の数式入力とオートフィル操作を確認していきます。
| A列 部署コード | B列 部署番号 |
|---|---|
| SALES-001 | SALES |
| ADMIN-002 | ADMIN |
文字数を固定してコピーする方法
続いては同じ文字数を全行から取り出す方法を確認していきます。
B2セルに=LEFT(A2,5)を入力し、下方向へオートフィルします。
部署コードの先頭がすべて5文字で統一されている場合、この方法でSALESやADMINを効率よく抽出できます。
第2引数の5は固定の数字なので、数式をコピーしても5のままです。
一方で第1引数のA2は相対参照のため、各行に応じてA3、A4へ変わります。
【操作のポイント】抽出したい文字数が全データで共通かどうかを、数式をコピーする前に確認します。
セルに文字数を指定する方法
続いては文字数を別セルで管理する方法を確認していきます。
たとえばD1セルに3と入力し、B2セルへ=LEFT(A2,$D$1)と入力します。
この式ではD1セルの数値を文字数として使用するため、抽出する桁数を後からまとめて変更できます。
$D$1のような絶対参照にすると、オートフィル後も参照先がD1セルに固定されます。
=LEFT(A2,$D$1)
商品分類のルール変更などで抽出桁数が変わる可能性がある表では、数値をセルに分けておく方法が有効です。
【操作のポイント】絶対参照は数式入力中にF4キーを押すと切り替えられます。
表示形式と抽出結果の確認
続いては抽出結果の表示形式を確認していきます。
LEFT関数の結果は文字列なので、セルを選択してホームタブの表示形式を確認すると文字列として扱われることがあります。
データをほかのシステムへ貼り付ける場合、先頭ゼロの有無や桁数が重要になることもあるでしょう。
抽出後は先頭行だけでなく、桁数が短いデータや例外データの行も確認することが大切です。
確認用に=LEN(B2)を使えば、抽出結果の文字数を数値で検査できます。
【操作のポイント】LEFT関数とLEN関数を並べると、指定どおりの文字数を取得できているかを確認しやすくなります。
LEFT関数とFIND関数を組み合わせる方法
続いてはLEFT関数とFIND関数を組み合わせる方法を確認していきます。
| A列 元データ | B列 区切り前 |
|---|---|
| 東京_営業部 | 東京 |
| 大阪_総務部 | 大阪 |
区切り記号の前を取り出す数式
続いては区切り記号の位置に応じて左側を取り出す数式を確認していきます。
文字数が一定でないデータでは、固定の文字数を指定するLEFT関数だけでは対応しにくい場合があります。
=LEFT(A2,FIND(“_”,A2)-1)
FIND関数でアンダースコアの位置を調べ、その直前までをLEFT関数で取り出す仕組みです。
FIND関数の結果から1を引くことで、区切り記号そのものを抽出結果に含めません。
【操作のポイント】区切り記号がハイフン、スラッシュ、空白の場合も、FIND関数内の文字を置き換えれば応用できます。
FIND関数でエラーになる場面
続いてはFIND関数でエラーになる場面を確認していきます。
A2セル内にアンダースコアが存在しない場合、FIND関数は位置を見つけられず、結果はエラーになります。
元データに表記ゆれや入力漏れがあるときは、IFERROR関数を組み合わせると見やすくなります。
=IFERROR(LEFT(A2,FIND(“_”,A2)-1),””)
この式では、区切り記号が見つからない場合に空白を表示します。
エラーを空白にするだけでなく、後で原因を確認できるよう「要確認」と表示する運用も有効です。
【操作のポイント】エラーを隠す前に、元データで区切り記号のルールが統一されているかを確認します。
SEARCH関数との使い分け
続いてはFIND関数とSEARCH関数の使い分けを確認していきます。
FIND関数は英字の大文字と小文字を区別しますが、SEARCH関数は区別しません。
たとえばabc-001とABC-001を同じ条件で探したい場合、文字列を探す用途ではSEARCH関数が便利です。
一方で、日本語の区切り記号やハイフンを探すだけなら、FIND関数でも十分に対応できます。
データの表記ルールが不統一な場合ほど、どの関数で位置を探すかが重要になります。
【操作のポイント】大文字と小文字を厳密に区別したいときはFIND関数、区別しないときはSEARCH関数を選びます。
LEFT関数で起こりやすいエラーと対処法
続いてはLEFT関数で起こりやすいエラーと対処法を確認していきます。
| 入力例 | 確認する内容 |
|---|---|
| =LEFT(A2,-1) | 文字数が0以上か |
| =LEFT(A2,C2) | C2が数値か |
文字数に負の数を指定した場合
続いては文字数に負の数を指定した場合を確認していきます。
=LEFT(A2,-1)のように第2引数へ負の数を指定すると、Excelではエラーになります。
LEFT関数は左から取り出す文字数を指定するため、マイナスの文字数という指定はできません。
右側から文字を取り出したい場合は、LEFT関数ではなくRIGHT関数を使います。
文字数を別セルから参照している場合は、そのセルにマイナス値や文字列が入力されていないかを確認しましょう。
【操作のポイント】抽出する文字数を利用者が入力する表では、入力規則で0以上の整数に制限すると安全です。
想定より短い文字列の場合
続いては元データが指定文字数より短い場合を確認していきます。
A2セルにABと入力されている状態で=LEFT(A2,5)を入力しても、エラーにはなりません。
この場合は存在する2文字だけが返され、結果はABになります。
LEFT関数は足りない文字を空白で補うのではなく、実際に存在する文字列だけを返します。
必ず5文字必要なコードを検査したい場合は、=IF(LEN(A2)<5,”桁数不足”,LEFT(A2,5))のようにLEN関数と組み合わせます。
【操作のポイント】短いデータが混じる可能性のある名簿やCSVでは、LEN関数による桁数確認を加えます。
数式が文字のまま表示される場合
続いては数式が計算されず文字のまま表示される場合を確認していきます。
セルに=LEFT(A2,3)と見えていて結果が表示されないときは、セルの表示形式が文字列になっている可能性があります。
表示形式を標準へ変更しただけでは再計算されないことがあるため、数式バーをクリックしてEnterキーを押しましょう。
先頭に半角アポストロフィが入っている場合も、数式は文字列として扱われます。
数式の先頭は必ず半角のイコール記号にし、全角の記号になっていないかも確認します。
【操作のポイント】数式が見えるときは、表示形式、先頭記号、Enterによる再確定の順に確認します。
まとめ エクセルのLEFT関数と数値の扱い
ExcelのLEFT関数は、セル内の文字列を左から指定した文字数だけ取り出すための基本的な文字列関数です。
=LEFT(A2,3)のように入力すれば、A2セルの先頭3文字を簡単に抽出できます。
商品コード、都道府県名、部署記号、番号の一部など、先頭側に意味を持つデータの整理で活用できるでしょう。
LEFT関数の結果は文字列として返るため、計算に使う場合はVALUE関数で数値化するかを検討します。
反対に、先頭ゼロを残すコードでは文字列のまま扱うことが重要です。
区切り文字の前を可変長で取り出したいときは、FIND関数やSEARCH関数との組み合わせが役立ちます。
まずは固定文字数の抽出から試し、オートフィル、絶対参照、エラー処理へと使い方を広げていきましょう。
