Excelで商品コード、住所、型番、伝票番号などを扱っていると、文字列の中に混在する数字だけを取り出したい場面があります。
たとえば「商品A-125-B」から「125」を抽出したり、「東京都千代田区1-2-3」から「123」を取り出したりできれば、集計や照合の作業が進めやすくなります。
数字の位置が毎回同じであればLEFT関数やMID関数でも対応できますが、文字数や数字の場所がばらばらの場合には、文字を1文字ずつ判定する数式が便利です。
この記事で扱うポイントは、TEXTJOIN関数とSEQUENCE関数による抽出、Microsoft 365以外で使える配列数式、フラッシュフィルやPower Queryを使う方法、抽出後に数値として利用するための注意点です。
サンプルデータは、1行目が見出しで、A列に元の文字列、B列に数字だけを抽出した結果を表示する形で解説します。
数字だけを文字列として抜き出すのか、計算できる数値として取り出すのかを先に決めることが、適切な数式選びにつながります。
それでは、文字列から数値を取り出す代表的な方法を確認していきましょう。
TEXTJOIN関数とSEQUENCE関数による数字抽出
それではまず、Microsoft 365やExcel 2021以降で使いやすい、TEXTJOIN関数とSEQUENCE関数を組み合わせた数字抽出について解説していきます。
| A列 元の文字列 | B列 数字のみ |
|---|---|
| 商品A-125-B | 125 |
| No.08-47 | 0847 |
| 東京3丁目12番地 | 312 |
基本数式の入力
まずB2セルを選択し、元データであるA2セルから半角数字だけを連結する数式を入力します。
=TEXTJOIN(“”,TRUE,IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,””))
数式を確定すると、「商品A-125-B」のように英字や記号を含む文字列から、125だけが取り出されます。
TEXTJOIN関数の最初の引数にある空文字列は、抽出した各数字を区切り文字なしでつなげる指定です。
TRUEは空白の値を無視する指定であり、文字や記号に該当する部分が結果に混ざらないように働きます。
この数式は数字が文字列の先頭、中間、末尾のどこにあっても対応できる点が大きなメリットです。
ただし、半角の0から9を対象にした数式なので、全角数字が含まれる場合は別途変換処理を検討しましょう。
【操作のポイント】数式をB2セルへ入力した後は、結果が文字列か数値かを確認し、用途に合わせてVALUE関数を追加します。
数式の仕組み
続いては、TEXTJOIN関数とSEQUENCE関数がどのように文字列を分解しているのかを確認していきます。
LEN(A2)はA2セルの文字数を数える関数で、「商品A-125-B」であれば文字列全体の長さを返します。
SEQUENCE(LEN(A2))は、1、2、3という連続した番号を、文字数分だけ配列として作成します。
MID(A2,SEQUENCE(LEN(A2)),1)は、その連続番号を開始位置として利用し、A2セルを1文字ずつ取り出す処理です。
各文字に*1を掛けると、半角数字は数値に変換され、英字、記号、漢字など数値に変換できない文字はエラーになります。
IFERROR関数はそのエラーを空文字列へ置き換え、最後にTEXTJOIN関数が数字だけを連結します。
文字列を1文字ずつ取り出す処理 → 数字へ変換できるか判定 → 数字だけを連結する処理という流れです。
数式の途中で作られる配列を意識すると、数字以外の文字を除外する考え方を他の抽出処理にも応用できます。
たとえば英字だけを残したい場合も、判定部分を変更することで似た考え方を利用できます。
【操作のポイント】数式を編集する際は、LEN関数で対象文字列の長さを取得していることを忘れないようにします。
オートフィルによる複数行への適用
続いては、入力済みの数式を下方向へコピーし、複数の文字列から数字だけを一括抽出する方法を確認していきます。
B2セルに数式を入力できたら、セル右下に表示される小さな四角形であるフィルハンドルをダブルクリックします。
A列に連続したデータがある場合、Excelはデータの最終行付近まで数式を自動的にコピーします。
マウス操作が難しい場合は、B2セルをコピーし、B3以降の必要な範囲を選択して貼り付けても構いません。
元データをテーブル形式にしておくと、新しい行を追加したときにも数式が自動で反映されるため、日々データが増える表に向いています。
抽出結果に先頭の0を残したい場合は、数値ではなく文字列として扱うことが重要です。
たとえば「No.08-47」は計算用の数値にすると847になることがありますが、管理番号として使うなら0847のまま残すほうが適切な場合があります。
【操作のポイント】先頭の0が意味を持つ番号では、抽出結果を数値化せず、セルの表示形式も文字列として管理します。
FILTER関数とLET関数による抽出式の整理
続いては、動的配列関数を使いながら、より読みやすく管理しやすい数字抽出の式を確認していきます。
| A列 元データ | B列 抽出結果 |
|---|---|
| 管理ID AB-2026-X | 2026 |
| 受付番号 R-00158 | 00158 |
| 棚番 C7-D04 | 704 |
LET関数を使った数式の可読性
続いては、長くなりやすい抽出数式に名前を付けるLET関数の使い方を確認していきます。
=LET(txt,A2,pos,SEQUENCE(LEN(txt)),ch,MID(txt,pos,1),TEXTJOIN(“”,TRUE,IFERROR(ch*1,””)))
この式では、txtにA2セルの内容、posに文字位置の連番、chに1文字ずつ取り出した結果を格納しています。
同じA2やMID関数を何度も書かずに済むため、式を後から見直すときに内容を理解しやすくなります。
LET関数は計算結果を変えるためというより、複雑な数式を保守しやすくするための関数です。
部署内でファイルを共有する場合や、半年後に自分で数式を修正する場合にも役立ちます。
【操作のポイント】変数名にはtxtやposのように、何を入れているか連想しやすい短い名称を使います。
抽出結果を数値へ変換する設定
続いては、取り出した数字を合計、並べ替え、大小比較に使える数値へ変換する方法を確認していきます。
TEXTJOIN関数の戻り値は、見た目が数字でも文字列として扱われることがあります。
計算に使う場合は、式全体をVALUE関数で囲みます。
=VALUE(TEXTJOIN(“”,TRUE,IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,””)))
この式なら、抽出した125を数値の125として扱えるため、SUM関数やAVERAGE関数の対象にできます。
一方で数字がまったく含まれない文字列では、VALUE関数がエラーを返す可能性があります。
空欄にしたい場合は、式全体をIFERROR関数で囲みます。
=IFERROR(VALUE(TEXTJOIN(“”,TRUE,IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,””))),””)
計算対象の列ではVALUE関数を使い、コードや電話番号の列では文字列のまま残すという使い分けが実務的です。
【操作のポイント】先頭の0を残す必要があるデータでは、VALUE関数を追加しないようにします。
全角数字を含むデータの前処理
続いては、全角の１、２、３を含む文字列から数字を取り出す前処理を確認していきます。
WebサイトやPDFからコピーした文字列には、半角数字と全角数字が混在することがあります。
ASC関数を使うと、全角の英数字や記号を半角へ変換できます。
=TEXTJOIN(“”,TRUE,IFERROR(MID(ASC(A2),SEQUENCE(LEN(ASC(A2))),1)*1,””))
ASC(A2)で文字列を半角化してから、数字の判定を実行する流れです。
ただし、ASC関数は日本語の環境設定や元データの内容によって意図しない変換が起きることもあるため、元データを残した列で試すと安心です。
変換前の元データ列を上書きせず、抽出用の列を追加して検証することをおすすめします。
【操作のポイント】全角と半角が混在する一覧では、最初にASC関数で統一してから抽出処理を行います。
MID関数とSUMPRODUCT関数による旧バージョン対応
続いては、SEQUENCE関数が使えないExcel 2019以前でも活用しやすい、MID関数とSUMPRODUCT関数を使う考え方を確認していきます。
| A列 元の文字列 | B列 数字部分 |
|---|---|
| 部品-482号 | 482 |
| 管理票A19B | 19 |
| ID-0607-XY | 0607 |
ROW関数を使った文字位置の作成
続いては、SEQUENCE関数の代わりにROW関数を使い、文字列を1文字ずつ取り出す準備を確認していきます。
旧バージョンでは、ROW($1:$100)のような記述で1から100までの連番を配列として利用できます。
元データが100文字を超えない想定なら、ROW($1:$100)を文字位置の一覧として使えます。
MID(A2,ROW($1:$100),1)とすると、A2セルの1文字目から100文字目までを1文字ずつ取り出します。
実際の文字数より後ろの位置には空文字列が返るので、その部分は後の処理で無視できます。
ROW($1:$100)の100は想定される最大文字数に合わせて調整する値です。
【操作のポイント】データが長い可能性がある場合は、ROW($1:$100)ではなくROW($1:$255)など余裕のある範囲にします。
配列数式による数字の連結
続いては、古いExcelで数字だけを抽出する配列数式の入力方法を確認していきます。
=TEXTJOIN(“”,TRUE,IFERROR(MID(A2,ROW($1:$100),1)*1,””))
Excel 2019などTEXTJOIN関数を使える環境では、SEQUENCE関数の部分をROW関数へ置き換えることで、似た結果を得られます。
さらに古いExcelでTEXTJOIN関数も使えない場合は、補助列を使う方法のほうが管理しやすいことがあります。
配列数式として入力する必要がある環境では、通常のEnterキーではなくCtrlキーとShiftキーを押しながらEnterキーを押して確定します。
数式バーに波かっこが自動表示される場合がありますが、波かっこを自分で入力する必要はありません。
Excelのバージョンによって確定方法が異なるため、数式が期待どおりに動かないときは動的配列への対応状況を確認します。
【操作のポイント】共有相手のExcelが古い場合は、SEQUENCE関数を含む数式を配布する前に互換性を確認します。
数式入力画面の確認
続いては、B2セルへ数式を入力し、オートフィルで下へコピーする操作画面を確認していきます。
画面ではB2セルが選択され、数式バーに数字抽出の式が表示されています。
数式を確定したら、B2セル右下のフィルハンドルを下方向へドラッグするか、ダブルクリックして式をコピーします。
数式バーの参照先がA2になっていることを確認してからコピーすると、ずれたセル参照によるミスを防げます。
【操作のポイント】先頭セルだけに数式を入力し、オートフィルで下へ展開すると、行ごとの参照先が自動で変わります。
フラッシュフィルと区切り位置による簡易抽出
続いては、数式を使わずに一度だけ数字を取り出したいときに便利な、フラッシュフィルと区切り位置の活用を確認していきます。
| A列 元データ | B列 手入力例 |
|---|---|
| 伝票No-125-A | 125 |
| 伝票No-208-B | 208 |
| 伝票No-319-C | 319 |
フラッシュフィルによる規則の認識
続いては、パターンがそろった文字列からフラッシュフィルで数字を取り出す方法を確認していきます。
B2セルに、A2セルの「伝票No-125-A」から取り出したい125を手入力します。
次のB3セルを選択し、データタブのフラッシュフィルをクリックするか、CtrlキーとEキーを押します。
ExcelがA列とB列の入力例から規則を推測できれば、下の行にも数字部分を自動で入力します。
フラッシュフィルは、データの並び方が一定で、抽出したい場所にも明確な規則がある場合に特に便利です。
ただし、データが追加された後に結果が自動更新されるわけではないため、継続的に使う表には数式のほうが向いています。
【操作のポイント】最初の入力例は、Excelが抽出規則を誤解しないよう、取り出したい形を正確に手入力します。
ハイフンで区切られた文字列の分割
続いては、ハイフンやスラッシュで区切られたデータを、区切り位置の機能で分ける方法を確認していきます。
たとえば「伝票No-125-A」のように、数字が必ずハイフンの間にある場合は、A列を選択してデータタブから区切り位置を実行します。
区切り文字としてハイフンを指定すると、文字列が複数列に分割され、中央の列に125を取り出せます。
元データを直接分割すると復元しにくくなるため、あらかじめ列をコピーして作業する方法が安全です。
区切り位置は、数字だけを抽出する機能ではなく、決まった記号で文字列を複数の列へ分ける機能です。
区切り記号が毎回同じなら、複雑な数式より区切り位置のほうが短時間で完了する場合があります。
【操作のポイント】区切り位置を実行する前に、分割先の右側列にデータがないか確認します。
フラッシュフィルが適さないケース
続いては、フラッシュフィルではなく数式を選んだほうがよいケースを確認していきます。
「商品A-125-B」「受付番号R-00158」「東京3丁目12番地」のように数字の位置や桁数が大きく異なるデータでは、Excelが規則を正しく認識できないことがあります。
同じ列に複数の数字の塊があり、どれを取り出すべきか判断が分かれる場合も、フラッシュフィルは不安定になりやすい傾向です。
また、フラッシュフィルの結果は値として入力されるため、A列を修正してもB列は自動で変わりません。
元データの更新に合わせて抽出結果も更新したい場合は、数式による抽出が必要です。
一度だけ整形して別システムへ渡す作業ならフラッシュフィル、継続利用する台帳なら数式というように選び分けましょう。
【操作のポイント】更新が発生する表では、抽出結果が数式であることを数式バーで確認します。
Power Queryによる大量データの整形
続いては、数千行以上のCSVや毎月届く一覧を整形するときに便利な、Power Queryを使った数字抽出の考え方を確認していきます。
| 元データ件数 | 向いている方法 |
|---|---|
| 少量で一度だけ | フラッシュフィル |
| 日常的に更新 | 数式 |
| 大量データを定期処理 | Power Query |
テーブルからPower Queryを起動する操作
続いては、Excelの表をPower Queryへ読み込む基本操作を確認していきます。
元データの範囲内を選択してCtrlキーとTキーを押し、先頭行をテーブルの見出しとして使う設定でテーブル化します。
次にデータタブからテーブルまたは範囲からを選択すると、Power Queryエディターが開きます。
Power Queryでは元の表を直接編集せず、取り込み時の変換手順として処理を記録できます。
毎月同じ形式のCSVを受け取る業務では、変換手順を再利用できるPower Queryが有力な選択肢です。
【操作のポイント】見出し行を含む範囲をテーブル化しておくと、列名を指定した変換が行いやすくなります。
数字以外の文字を除く考え方
続いては、Power Queryで文字列を変換するときの考え方を確認していきます。
Power Queryでは、列の追加からカスタム列を作成し、文字列をリストとして扱いながら必要な文字だけを選別できます。
数式と同じく、各文字が数字の範囲に含まれるかを判定し、該当する文字を結合する方法が基本になります。
操作に慣れるまでは難しく感じるかもしれませんが、一度作成したクエリは更新ボタンで再実行できます。
データ量が多い場合、ワークシートに非常に長い配列数式を大量に入れるより、処理を分けて管理しやすいことがあります。
【操作のポイント】Power Queryでは元の列を残し、新しい列として抽出結果を作成して検証します。
更新時の確認項目
続いては、Power Queryで処理を更新する際に確認したい項目を見ていきます。
新しいCSVを同じフォルダーに置き換えた後、Excel上で更新を実行すると、設定済みの変換手順を再適用できます。
ただし、元データの列名が変わった場合や、数字の表記ルールが変わった場合は、クエリの手順がエラーになる可能性があります。
更新前後で件数、空欄数、先頭の0が消えていないかを確認する習慣を付けると、変換ミスを早期に見つけられます。
住所や電話番号のように個人情報を含むデータでは、抽出結果の共有先や保存場所にも注意が必要です。
【操作のポイント】更新後は最初の数行だけでなく、抽出できない行や例外データも抽出して確認します。
抽出結果のエラーとデータ形式の確認
続いては、数字だけを抽出した後に起こりやすいエラー、先頭の0、日付への自動変換といった注意点を確認していきます。
| 抽出結果の例 | 確認したい点 |
|---|---|
| 00158 | 先頭の0を残すか |
| 20261008 | 日付として扱うか |
| 空欄 | 数字がない場合の表示 |
先頭のゼロが消える原因
続いては、00158のような結果から先頭の0が消えてしまう原因を確認していきます。
Excelでは数値として扱われた値は、通常、先頭の0を表示しません。
そのため、VALUE関数を使ったり、セルの表示形式が標準になったりすると、00158は158と表示されます。
受付番号、郵便番号、社員番号、製品コードなどは計算用の値ではなく識別子であることが多いため、文字列として保存するのが基本です。
先頭の0が必要な番号は、抽出式を文字列のまま使い、セルの表示形式も文字列に設定します。
番号に見えても足し算や平均計算をしないデータは、数値ではなく文字列として管理する考え方が大切です。
【操作のポイント】番号列ではVALUE関数を使わず、数式を入力する前に表示形式を文字列にする方法も有効です。
数字が含まれない場合の処理
続いては、元の文字列に数字がない場合のエラーや空欄処理を確認していきます。
「未設定」や「対象外」のように数字が1つも含まれないセルでは、TEXTJOIN関数の結果が空文字列になります。
そのままVALUE関数で数値に変換すると、空文字列を数値にできずエラーになることがあります。
この場合は、IFERROR関数を使ってエラー時に空欄や0を表示するよう設定します。
=IFERROR(VALUE(TEXTJOIN(“”,TRUE,IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,””))),””)
空欄にするか0にするかは、数字が存在しないことと値が0であることを同じ意味として扱ってよいかで決めます。
未入力とゼロを区別する必要がある一覧では、エラー時に0ではなく空欄を返す設定が安全です。
【操作のポイント】エラー処理を追加した後は、数字を含まないテスト用のデータでも結果を確認します。
複数の数字グループを分ける方法
続いては、「2026年10月8日」のように複数の数字の塊がある文字列を扱う際の注意点を確認していきます。
ここまでの数式では、2026、10、8がすべて連結され、2026108という1つの結果になります。
年、月、日を別々の列へ取り出したい場合や、「商品12 型番45」の12と45を分けたい場合は、数字だけを連結する方法では目的に合いません。
区切り記号が一定であればTEXTSPLIT関数、日付形式が一定であればMID関数と位置指定、複雑なデータではPower Queryを検討します。
数字を全部つなげる抽出と、数字の塊ごとに分ける抽出は別の処理なので、完成形を先に決めてから数式を選びましょう。
【操作のポイント】抽出後に何列へ分けたいか、各数字が何を意味するかを表の見出しで明確にします。
まとめ Excelで文字列から数字だけを抽出する方法
Excelで数字だけを抽出する基本方法は、TEXTJOIN関数、MID関数、SEQUENCE関数を組み合わせ、文字列を1文字ずつ判定して数字だけを連結する方法です。
Microsoft 365やExcel 2021以降では、=TEXTJOIN(“”,TRUE,IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,””))という式を使うと、数字の位置がばらばらな文字列にも対応できます。
計算に使う結果ならVALUE関数を追加し、郵便番号や管理番号のように先頭の0を残したい結果なら文字列のまま扱いましょう。
Excel 2019以前では、SEQUENCE関数の代わりにROW($1:$100)などを使う方法があります。
データの規則がそろっていて一度だけ処理する場合はフラッシュフィル、定期的に大量データを整形する場合はPower Queryも便利です。
重要なのは、数字だけを抽出した後に、その値を計算用の数値として使うのか、識別用の文字列として残すのかを判断することです。
元データの形式と利用目的に合わせて抽出方法を選び、Excelでの集計やデータ整形を効率化していきましょう。
