【Excel】エクセルで数字と文字を分ける方法（関数・セル分割）

Excelで商品コード、型番、住所、氏名と番号が混在したデータを扱っていると、数字だけを抽出したい場面や、文字だけを別セルへ取り出したい場面があります。

たとえば「A-120」「東京03-1234-5678」「商品ABC500」のような文字列は、そのままでは集計、並べ替え、計算に使いにくいことがあります。

関数で分ける方法と、区切り位置を使ってセルを分割する方法を使い分けることが、作業を正確かつ効率的に進めるコツです。

この記事では、1行目に見出しがある一覧表を例に、Excelで数字と文字を安全に分ける手順を解説していきます。

元データを直接変更する前に、コピーした列で試すと失敗を防ぎやすくなります。

 

区切り位置による数字と文字のセル分割

それではまず、ハイフン、スペース、スラッシュなどの区切り記号を基準に、数字と文字を別々のセルへ分ける方法について解説していきます。

A列 商品コード	B列 区分	C列 番号
AB-120	AB	120
CD-305	CD	305
EF-42	EF	42

 

区切り位置を実行する前の準備

区切り位置は、1つのセルに含まれる文字列を指定したルールで複数列へ展開するExcelの機能です。

「AB-120」のように、文字部分と数字部分の間に全行で同じハイフンが入っているデータなら、数式を書かずに短時間で分割できます。

まず、分割したいデータが入力されたA列を選択します。

見出しを除いてA2から選択しても、A列全体を選択しても問題ありません。

ただし、右側のB列やC列に値がある場合、分割結果によって上書きされる可能性があります。

必要なら空いている列を挿入してから操作しましょう。

数字を数値として計算する予定なら、分割後の列の表示形式も確認しておくとよいでしょう。

 

ハイフンを指定する区切り位置の設定

続いては、データタブから区切り位置を開き、ハイフンを区切り文字として指定する操作を確認していきます。

Excel上部のデータタブを選択し、データツールグループにある区切り位置をクリックします。

ウィザードが表示されたら、最初の画面では「区切り文字によってフィールドごとに区切られたデータ」を選び、次へ進みます。

次の画面で「その他」にチェックを入れ、その入力欄に半角ハイフンを入力してください。

タブやスペースのチェックが残っていると意図しない場所でも分割されるため、必要のない項目は外しておきます。

プレビュー欄で「AB」と「120」の間に縦線が表示されれば、設定は適切です。

電話番号のようにハイフンが複数ある文字列では、すべてのハイフンで分かれる点に注意が必要です。

 

分割先とデータ形式の指定

続いては、分割結果を配置する先と、数値列の形式を確認していきます。

最後の画面では、分割後にできる各列をプレビュー上で選択できます。

番号を先頭ゼロなしの数値として扱うなら「標準」のままで構いません。

一方で「0012」のような管理番号をそのまま表示したい場合は、対象列を選択して「文字列」を指定します。

出力先にB2を指定すると、A2の「AB-120」はB2の「AB」とC2の「120」に分かれます。

完了を押す前に、出力先の右側に必要な空白列があるか確認しましょう。

 

LEFT関数とRIGHT関数による固定位置の抽出

続いては、文字数と数字の位置が毎行同じ場合に便利なLEFT関数、RIGHT関数、MID関数について確認していきます。

A列 型番	B列 英字部分	C列 数字部分
ABC120	ABC	120
DEF305	DEF	305
GHI042	GHI	042

 

LEFT関数で左側の文字を取り出す方法

それではまず、文字列の左端から指定した文字数を取り出すLEFT関数を解説していきます。

ABC120の先頭3文字であるABCをB2へ表示する場合、B2に次の数式を入力します。

LEFT関数は、LEFT（文字列,取り出す文字数）という形で指定します。

この例ではA2が文字列、3が左から取り出す文字数です。

英字部分が常に3文字であることが、この数式を使う前提になります。

数式を確定したら、B2右下の小さな四角形であるフィルハンドルを下へドラッグし、B列のデータ行までコピーします。

オートフィルでは、A2という参照がA3、A4のように自動で変わるため、各行の型番に対応した文字を抽出できます。

 

RIGHT関数で末尾の数字を取り出す方法

続いては、文字列の右端から指定した文字数を取り出すRIGHT関数を確認していきます。

型番の末尾3文字をC2へ表示するには、次の数式を入力します。

RIGHT関数は、RIGHT（文字列,取り出す文字数）という構成です。

「GHI042」の場合、結果は042と表示されますが、計算用に数値へ変換したい場合はVALUE関数を組み合わせます。

この式の結果は42となり、先頭のゼロは表示されなくなります。

管理番号として先頭ゼロが重要ならVALUE関数は使わないことが大切です。

番号を加算、平均、比較などに利用する場合だけ、数値化を検討しましょう。

 

MID関数で途中の文字を取り出す方法

続いては、文字列の途中にある数字や文字を抽出できるMID関数を解説していきます。

たとえば「AB120CD」から中央の120だけを取り出す場合、開始位置と文字数を指定します。

MID関数は、MID（文字列,開始位置,取り出す文字数）という書式です。

開始位置は左端を1として数えるため、この例では3文字目から3文字を取得します。

文字数が決まった管理コードでは、LEFT関数、MID関数、RIGHT関数を組み合わせると列ごとに必要な情報を整えられます。

 

TEXTBEFORE関数とTEXTAFTER関数による区切り文字の抽出

続いては、Microsoft 365やExcel 2021以降で利用しやすいTEXTBEFORE関数とTEXTAFTER関数を使う方法を確認していきます。

A列 管理コード	B列 前半	C列 後半
東京-1001	東京	1001
大阪-2050	大阪	2050
福岡-0315	福岡	0315

 

TEXTBEFORE関数で区切り文字より前を抽出する方法

それではまず、指定した区切り文字より左側を取り出すTEXTBEFORE関数を解説していきます。

B2に次の数式を入力すると、A2のハイフンより前にある文字列を取得できます。

この関数では、抽出したい文字列を最初に指定し、次に境界となる区切り文字を指定します。

「東京-1001」なら結果は東京となり、文字数を数える必要がありません。

前半の文字数が行ごとに異なっても、区切り記号が共通なら対応できる点が大きな利点です。

ハイフンが全角と半角で混在していると結果が分かれるため、データの表記をあらかじめ統一しておきましょう。

 

TEXTAFTER関数で区切り文字より後を抽出する方法

続いては、区切り文字より右側を取り出すTEXTAFTER関数を確認していきます。

C2には、次の数式を入力します。

TEXTAFTER関数は、最初に見つかったハイフンの後ろを返します。

結果の1001は文字列として扱われるため、数値計算に利用するならVALUE関数で包みます。

ただし、0315のようにゼロから始まる番号は315に変わるため、帳票やコード管理では文字列のまま扱うほうが適切かもしれません。

データの目的に応じて、見た目の保持と計算のしやすさを選びましょう。

 

数式バーとオートフィルの操作イメージ

続いては、TEXTAFTER関数を入力して下の行へコピーする画面イメージを確認していきます。

数式をC2に入力したら、セル右下のフィルハンドルを最終行までドラッグします。

データが連続している場合は、フィルハンドルをダブルクリックして自動コピーする方法も便利です。

 

数字だけを取り出す関数の組み合わせ

続いては、英字と数字が混在し、位置や桁数が行ごとに異なるデータから数字だけを抽出する考え方を確認していきます。

A列 元データ	B列 抽出した数字
商品A120	120
部品B305	305
型番C42	42

 

SEQUENCE関数を使った文字の分解

それではまず、文字列を一文字ずつ扱うためのSEQUENCE関数とMID関数の考え方を解説していきます。

Microsoft 365では、文字列の長さに合わせて連番を作り、MID関数で一文字ずつ取り出す方法があります。

文字数はLEN関数で確認できます。

「商品A120」なら、LEN関数の結果は6です。

SEQUENCE関数で1から6までの位置を作ると、MID関数は各位置の文字を順番に取り出せます。

この方法は式が長くなりますが、数字が何文字あるか事前に分からないデータにも対応できるのが特徴です。

 

数字を連結して数値へ変換する数式

続いては、取り出した数字だけをつなげて、ひとつの値にする数式を確認していきます。

Microsoft 365でA2から数字だけを抽出する場合は、次のような数式を利用できます。

SEQUENCEで文字位置を作り、MIDで一文字ずつ取り出し、数値に変換できる文字だけを残しています。

TEXTJOIN関数は残った数字を空白なしで連結し、VALUE関数で最終的に数値へ変換します。

この式は動的配列に対応したExcelで使いやすい書き方です。

数字が存在しないセルではエラーになる可能性があるため、実務ではIFERROR関数を外側に付けると扱いやすくなります。

 

抽出結果を確認するときの注意点

続いては、数字抽出後に確認したいデータ上の注意点を解説していきます。

「A12B34」から数字だけを抽出すると、結果は1234になります。

途中の文字を境界として12と34を別々に扱いたい場合、この式だけでは目的に合いません。

また、負の数を表すマイナス記号、小数点、単位としての数字が含まれるデータも慎重な判断が必要です。

電話番号、郵便番号、社員番号などは数値に見えても、計算の対象ではなく識別子であることが少なくありません。

抽出後の値を計算に使うのか、表示と検索に使うのかを先に決めると、数値化の可否を判断しやすくなります。

 

文字だけを取り出す関数と置換の考え方

続いては、数字を除いて文字だけを取り出したい場合の関数の考え方と、データ整理の注意点を確認していきます。

A列 元データ	B列 文字部分
商品A120	商品A
部品B305	部品B
型番C42	型番C

 

固定桁データから文字部分を求める方法

それではまず、数字が必ず末尾にあり、その桁数が決まっている場合の簡単な方法を解説していきます。

「商品A120」の末尾3桁を数字とみなすなら、左側を取り出すLEFT関数で文字部分を取得できます。

LEN関数で元の文字数を数え、そこから数字3桁を引いた数だけ左から取り出す式です。

この書き方なら、商品名の文字数が行ごとに違っても、末尾の数字が3桁である限り対応できます。

数字の桁数が2桁や4桁に変わるデータには、そのまま使えない点には注意してください。

 

数字を除外して文字を連結する方法

続いては、数字の位置が決まっていない文字列から、文字だけを残す方法を確認していきます。

Microsoft 365では、数字抽出と同じ考え方で一文字ずつ判定し、数値ではない文字をTEXTJOIN関数で連結できます。

数値へ変換できる文字は除外され、変換できない文字だけが残る仕組みです。

全角数字、半角数字、記号、空白をどのように扱うかで結果が変わる場合があります。

データに全角数字が含まれる場合は、ASC関数や置換機能で表記を統一してから処理すると安定します。

 

空白と記号を整理する手順

続いては、抽出した文字列に残りやすい空白や記号を整える手順を解説していきます。

不要な半角スペースはTRIM関数で連続した空白を整理できます。

特定のハイフンやスラッシュを消したいときはSUBSTITUTE関数が便利です。

ただし、商品名のハイフンのように意味を持つ記号まで削除すると、検索性や表記の正確性が下がることがあります。

置換は元データではなく抽出結果の列で行うと、後から検証しやすくなります。

 

セル分割と関数を使い分ける判断基準

続いては、区切り位置、関数、フラッシュフィルのどれを選ぶべきかを、データの性質から確認していきます。

データの状態	向いている方法
ハイフンや空白が一定	区切り位置
文字数と位置が固定	LEFT、RIGHT、MID関数
区切り位置が可変	TEXTBEFORE、TEXTAFTER関数
規則が不均一	抽出関数または目視確認

 

元データを更新するかどうかの違い

それではまず、区切り位置と関数による分割の大きな違いを解説していきます。

区切り位置は、実行した時点でセルの内容を複数列へ展開する操作です。

元データが更新されても、分割結果が自動で変わるわけではありません。

一方、関数で作った列はA列の内容が変わると結果も再計算されます。

定期的に追加や修正が発生する一覧には、関数による分割が向いていると考えられます。

一度だけ加工して別ファイルへ渡すデータなら、区切り位置の操作でも十分です。

 

フラッシュフィルを使える場面

続いては、入力例からExcelが規則を推測するフラッシュフィルの使いどころを確認していきます。

B2に「AB-120」から取り出したいABを手入力し、B3で同じように入力を始めると候補が表示されることがあります。

候補が正しければEnterキーで確定でき、CtrlキーとEキーでフラッシュフィルを実行することも可能です。

関数を覚えていない場合には便利ですが、複雑な規則や表記ゆれでは誤った推測をすることがあります。

大量データに適用した後は、先頭、中間、末尾の行を確認しましょう。

フラッシュフィルの結果は数式ではなく値として入力されるため、元データを修正しても自動更新されません。

 

エラーと表記ゆれの確認方法

続いては、分割処理で起こりやすいエラーと表記ゆれを確認していきます。

TEXTBEFORE関数で区切り文字が見つからない場合、通常はエラー表示になります。

未入力や例外行が混ざる一覧では、IFERROR関数で空白を返すようにしておくと表が見やすくなります。

半角ハイフン、全角ハイフン、長音記号は見た目が似ていますが、Excelでは別の文字です。

空白も半角と全角が混在しやすいため、抽出できない行があれば文字の種類を確認してください。

 

まとめ エクセルでセル分割と数字と文字を分ける方法

Excelで数字と文字を分ける方法は、データに区切り文字があるか、文字数が固定か、位置が不規則かによって選ぶ方法が変わります。

ハイフンやスペースなどが共通しているデータは、区切り位置を使うと素早くセル分割できます。

文字数が固定された型番にはLEFT関数、RIGHT関数、MID関数が扱いやすい選択肢です。

区切り文字の位置が行ごとに異なる場合は、TEXTBEFORE関数とTEXTAFTER関数で境界を指定する方法が有効です。

数字だけ、または文字だけを抽出する処理では、抽出後の値を計算用の数値にするのか、先頭ゼロを保持する文字列にするのかを必ず確認しましょう。

まずはコピーしたデータで数式や区切り位置を試し、結果を数行確認してから全体へ反映する流れがおすすめです。