Excelのデータには、商品コードの「AB123」、サイズ表記の「M250」、住所の「東京都123」のように、数字と文字列が同じセルに入っている場面があります。
このような文字列を数字部分と文字部分に分割できれば、並べ替え、集計、検索、計算がしやすくなります。
関数を使えば、元データを直接書き換えずに数字と文字を別セルへ取り出せます。
数字と文字を分ける主な方法は、文字数が決まっている場合のLEFT関数やRIGHT関数、区切り位置が決まっている場合のMID関数、数字の位置が不規則な場合のTEXTSPLIT関数や配列数式です。
この記事では、1行目に見出しがある表を例にしながら、エクセルで数字と文字を分ける関数の使い方を詳しく解説します。
エクセルで数字と文字を分ける基本関数
それではまず、数字と文字が決まった位置にあるデータを分ける基本関数について解説していきます。
| 商品コード | 文字部分 | 数字部分 |
|---|---|---|
| AB123 | AB | 123 |
| CD045 | CD | 045 |
| EF900 | EF | 900 |
LEFT関数で先頭の文字を取り出す方法
LEFT関数は、セルの左端から指定した文字数だけを取り出す関数です。
たとえばA2セルに「AB123」があり、先頭2文字の英字をB2セルに表示したい場合に使います。
=LEFT(A2,2)
この数式では、A2セルの左側から2文字を取得するため、結果は「AB」になります。
LEFT関数の第2引数には、取り出したい文字数を指定します。
英字が常に2文字である商品コードなら、B2セルの数式を下方向へオートフィルするだけで、各行の文字部分をまとめて分割できます。
先頭の文字数がデータごとに変わる場合は、LEFT関数だけでは対応しにくいため、後述する応用関数を検討しましょう。
RIGHT関数で末尾の数字を取り出す方法
RIGHT関数は、セルの右端から指定した文字数を取り出す関数です。
A2セルの「AB123」から末尾3文字の数字を取り出す場合は、C2セルへ次の数式を入力します。
=RIGHT(A2,3)
表示結果は「123」になりますが、この段階では文字列として扱われることがあります。
取り出した数字で合計や平均を計算するなら、VALUE関数を組み合わせる方法が便利です。
=VALUE(RIGHT(A2,3))
VALUE関数を加えると、文字列の「123」を計算可能な数値の123へ変換できます。
ただし「045」のような先頭のゼロを表示したいコードでは、VALUE関数を使うと「45」になるため注意が必要です。
LEN関数と組み合わせる分割の考え方
文字部分の長さが固定で、数字部分の桁数だけが変わる場合は、LEN関数をRIGHT関数と組み合わせます。
たとえば「AB7」「AB12345」のように、先頭2文字だけが共通しているデータに向く方法です。
=RIGHT(A2,LEN(A2)-2)
LEN関数はセル内の文字数を返すため、全体の文字数から先頭2文字を引けば、数字部分の長さを自動計算できます。
固定されている側の文字数を基準にすると、可変長の反対側を安全に抜き出せます。
一方で、英字の文字数も数字の桁数も行ごとに異なるデータでは、この考え方だけでは不足します。
【操作のポイント】先頭や末尾の文字数が固定かどうかを、数式を作る前に数行分確認します。
区切り文字を使った数字と文字の分割
続いては、ハイフンやスペースなどの区切り文字を含むデータを分ける方法を確認していきます。
| 管理番号 | 部門 | 番号 |
|---|---|---|
| 東京-125 | 東京 | 125 |
| 大阪-220 | 大阪 | 220 |
| 福岡-008 | 福岡 | 008 |
TEXTBEFORE関数で区切り前を取り出す方法
Microsoft 365やExcel 2021以降では、TEXTBEFORE関数を使うと区切り文字より前の文字列を簡潔に取り出せます。
A2セルに「東京-125」がある場合、B2セルには次のように入力します。
=TEXTBEFORE(A2,”-“)
結果は「東京」です。
TEXTBEFORE関数は、指定した文字が最初に出現する位置より前を返します。
ハイフンのほか、半角スペース、全角スペース、スラッシュなども区切りとして指定できます。
ただしデータ内に区切り文字がない行があるとエラーになるため、入力規則が統一されているか確認しておくと安心です。
TEXTAFTER関数で区切り後を取り出す方法
区切り文字より後ろの数字を取得するなら、TEXTAFTER関数を使います。
C2セルに入力する数式は次のとおりです。
=TEXTAFTER(A2,”-“)
「東京-125」からは「125」が返されます。
取得した値を計算に使う場合は、VALUE関数を外側に付けて数値へ変換できます。
=VALUE(TEXTAFTER(A2,”-“))
ゼロ埋め番号を管理番号として残すなら、VALUE関数を付けず文字列のまま扱うことが大切です。
番号が「008」のように表示される必要がある帳票では、文字列のまま取り出すほうが実務に合います。
FIND関数とMID関数で旧バージョンに対応する方法
TEXTBEFORE関数やTEXTAFTER関数が使えないExcelでは、FIND関数とMID関数を組み合わせます。
FIND関数は文字列内で指定文字が何文字目にあるかを返し、MID関数は途中から指定文字数を抜き出します。
=LEFT(A2,FIND(“-“,A2)-1)
この数式はハイフンの位置を調べ、ハイフンの直前までを取り出す式です。
=MID(A2,FIND(“-“,A2)+1,LEN(A2))
こちらはハイフンの次の文字から末尾までを取り出します。
古いExcelでも使える数式を作りたい場合、FIND関数とMID関数の組み合わせは覚えておくと役立ちます。
【操作のポイント】区切り文字が全角と半角で混在していないかを確認し、実データと同じ文字を数式に入力します。
数字の位置が不規則なデータの分割
次は、文字数や数字の開始位置が行ごとに異なるデータを分ける方法を解説していきます。
| 品名コード | 文字部分 | 数字部分 |
|---|---|---|
| A12B | AB | 12 |
| 部品7号 | 部品号 | 7 |
| X300Z5 | XZ | 3005 |
SEQUENCE関数で文字を一文字ずつ判定する考え方
数字が途中にある場合は、文字列を一文字ずつ確認して、数字だけを結合する方法が有効です。
Microsoft 365ではSEQUENCE関数、MID関数、TEXTJOIN関数を組み合わせられます。
=TEXTJOIN(“”,TRUE,IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,””))
この式はA2セルの各文字を順番に数値化し、数値として扱える文字だけを残して結合します。
MID関数で一文字ずつ取り出し、数値に変換できない文字をIFERROR関数で除外する仕組みです。
結果は「A12B」なら12、「X300Z5」なら3005になります。
【操作のポイント】Microsoft 365以外では動作しない場合があるため、勤務先や共有先のExcelバージョンを確認します。
LET関数を使った読みやすい数式
長い数式を扱うなら、LET関数で途中の計算結果に名前を付けると見直しやすくなります。
数字だけを取り出す数式は、次のように整理できます。
=LET(x,MID(A2,SEQUENCE(LEN(A2)),1),TEXTJOIN(“”,TRUE,IFERROR(x*1,””)))
ここではxという名前に、A2セルから一文字ずつ取り出した配列を格納しています。
LET関数は計算を分解して記述できるため、複雑な分割式の保守性を高めます。
文字列の長さが長いデータを大量に処理する場合にも、数式の意味を共有しやすくなるでしょう。
Excel画面で数式を入力してオートフィルする操作
数式を入力したら、先頭データで正しい結果になることを確認してから、フィルハンドルで下の行へコピーします。
| A | B | C | |
|---|---|---|---|
| 1 | 品名コード | 文字部分 | 数字部分 |
| 2 | A12B | AB | 12 |
| 3 | 部品7号 |
赤枠で示したC2セルに数式を入力し、セル右下の小さな四角を下方向へドラッグします。
数式内のA2は相対参照なので、コピー先ではA3、A4のように自動で行番号が変わります。
結果がおかしい行だけがある場合は、元データに全角数字、空白、記号が含まれていないかを確認しましょう。
【操作のポイント】最初の数式は必ず1行だけで検証し、想定どおりの表示を確認してからオートフィルします。
文字部分だけを抽出する関数
続いては、数字を除いた文字だけを別セルに取り出す方法を確認していきます。
| 元データ | 抽出する文字 |
|---|---|
| A12B | AB |
| 部品7号 | 部品号 |
| X300Z5 | XZ |
数字以外を残すTEXTJOIN関数
文字部分の抽出では、数字だけを抽出する式とは逆に、数値へ変換できない文字を残します。
=TEXTJOIN(“”,TRUE,IF(ISERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1),MID(A2,SEQUENCE(LEN(A2)),1),””))
この数式は、一文字ずつ数値化を試し、エラーになる英字や日本語だけをTEXTJOIN関数でつなげます。
数字と文字が複数回交互に現れる形式でも、文字だけをまとめて取り出せます。
「A12B」の結果は「AB」、「部品7号」の結果は「部品号」です。
全角数字が混在する場合の注意点
「商品１２３」のように全角数字が含まれる場合、半角数字を前提にした数式では判定が期待どおりにならないことがあります。
先にASC関数で半角へ変換してから分割すると、表記ゆれを減らせます。
=ASC(A2)
ASC関数は全角の英数字や記号を半角へ変換するため、取り込みデータの整形に便利です。
ただし日本語文字は半角化されないため、品名や部署名が変わる心配は通常ありません。
抽出後のデータを値として固定する方法
関数で分けた結果を別ファイルへ渡す場合や、元データを削除する予定がある場合は、数式を値へ変換します。
対象セルをコピーし、貼り付け先で右クリックして「値の貼り付け」を選択します。
値に変換すると、元のA列を修正しても分割済みのB列やC列の結果は変わりません。
計算式を残すべきか、確定値にすべきかは、データ更新の予定で判断します。
【操作のポイント】元データとの連動が必要な間は数式を残し、提出用や保存用の段階で値へ変換します。
関数で分けた数字と文字の活用
ここでは、分割後の列を実務で使いやすくする考え方を解説していきます。
| 商品コード | 分類 | 連番 | 活用例 |
|---|---|---|---|
| AB123 | AB | 123 | 分類別集計 |
| CD045 | CD | 45 | 連番順の並べ替え |
文字列と数値の表示形式の違い
同じ「123」に見えても、Excelでは文字列と数値で扱いが異なります。
計算や数値順の並べ替えをする列は、VALUE関数などで数値にしておくと誤動作を防ぎやすくなります。
一方、郵便番号、社員番号、商品コードのように先頭ゼロが意味を持つ項目は文字列として保持します。
用途に応じて、数字の見た目ではなくデータ型を選ぶことが重要です。
フィルターと並べ替えへの利用
文字部分を独立した列にすると、部門名や商品分類でフィルターをかけやすくなります。
数字部分を数値列にすれば、「2、10、100」のように正しい数値順で並べ替えられます。
元のコード列だけでは絞り込みにくいデータも、補助列を作ることで分析しやすくなるでしょう。
大量データでは、元データを残したまま分割列を追加する方法が安全です。
エラーを防ぐIFERROR関数の使い方
空欄や区切り文字のないデータが混じる表では、数式のエラー表示を抑えるためにIFERROR関数を使います。
=IFERROR(TEXTAFTER(A2,”-“),””)
この数式では、A2セルにハイフンがない場合でもエラーではなく空欄が表示されます。
IFERROR関数は見た目を整えるだけでなく、例外データを見つけにくくする面もあります。
重要なデータでは空欄に隠すだけでなく、別列で元データを確認し、入力漏れや表記ゆれを修正しましょう。
【操作のポイント】エラーを空欄にした後は、フィルターで空欄を抽出し、原因となった元データを確認します。
まとめ エクセルで数字と文字を分ける関数の方法
エクセルで数字と文字を分けるには、データの並び方に合わせて関数を選ぶことが基本です。
先頭や末尾の文字数が固定なら、LEFT関数、RIGHT関数、LEN関数の組み合わせで効率よく分割できます。
ハイフンやスペースがある形式では、TEXTBEFORE関数、TEXTAFTER関数、またはFIND関数とMID関数が役立ちます。
数字の位置が不規則な場合は、SEQUENCE関数、MID関数、TEXTJOIN関数を使って一文字ずつ判定する方法を試しましょう。
取り出した数字を計算に使う場合はVALUE関数で数値化し、先頭ゼロを残したい番号は文字列のまま管理します。
元データの規則性を見極めてから数式を選べば、複雑に見えるコードや品番も整理しやすくなります。
