Excelで文字列から型番・品番だけを抽出する方法｜関数や正規表現で大量データを整理

商品名や説明文の中に混ざっている型番や品番だけを抜き出したい場合、手作業で探すのはデータ量が多くなるほど大変になります。Excelでは関数や正規表現を利用することで、文章の先頭・途中・末尾など場所がバラバラでも、一定のルールに合う文字列だけを抽出できます。

この記事では、例えば「りんご果物 HT3005」「YASAI OK5002」「A5002 POTETO」のようなデータから、型番と思われる英数字だけを自動抽出する方法を詳しく解説します。

型番抽出で重要になる文字列の特徴を確認する

型番を自動で取り出すには、まず型番に共通する特徴を見つける必要があります。今回の例では「英字＋数字」という形式になっています。

例えば以下のようなデータの場合、抽出したい部分は次のようになります。

このように型番の位置が決まっていない場合、LEFT関数やRIGHT関数だけでは対応できません。そのため、文字列の中から条件に一致する部分を探す方法が必要になります。

Microsoft 365やExcelの新しいバージョンでは、正規表現を利用できる関数が追加されています。REGEXEXTRACT関数を使うと、条件に一致する文字列だけを取得できます。

例えば、A1セルにデータが入っている場合、以下のような式で英字と数字が組み合わさった型番を抽出できます。

=REGEXEXTRACT(A1,"[A-Z]+[0-9]+")

この式では「アルファベットが1文字以上続き、その後に数字が続く文字列」を検索します。そのため、HT3005、A5002、OK5002などを取得できます。

1万件以上のデータでも、数式を下方向へコピーするだけで一括処理できるため、大量の商品データ整理にも向いています。

古いExcelではREGEXEXTRACT関数が利用できないため、別の方法を使います。その場合は、文字列を1文字ずつ確認して条件に合う部分を探す数式を作成します。

例えば、Power Queryを利用すると正規表現に近い処理が可能になります。大量データの場合は関数を複雑にするより、Power Queryで変換処理を作成する方が管理しやすい場合があります。

また、VBAを利用できる環境であれば、正規表現オブジェクトを使って「英字＋数字」のパターンだけを抽出するマクロを作成することも可能です。

大量のデータを定期的に処理する場合は、ExcelのPower Queryがおすすめです。データ取り込み時に型番抽出のルールを設定しておけば、更新ボタンを押すだけで最新データを処理できます。

例えば、毎月商品マスタを更新する業務では、毎回関数をコピーするよりもPower Queryで抽出処理を作成しておくことで作業時間を大幅に短縮できます。

Power Queryでは不要な文字列を削除したり、列を分割したりする処理も組み合わせられるため、型番の位置が一定しないデータにも対応できます。

英字＋数字という条件だけで抽出すると、商品名の一部や管理番号など、本来不要な文字列まで取得してしまう場合があります。

例えば「サイズA5002」「商品X100」のような文字列が存在する場合は、型番の桁数や使用される英字の種類など、より具体的な条件を追加すると精度が向上します。

実際の業務データでは、型番の形式が「英字2文字＋数字4桁」「数字のみ」「ハイフンを含む」など会社ごとに異なるため、データの規則に合わせて正規表現を調整することが重要です。

Excelで文章の中から型番だけを抽出する場合、データ内の共通ルールを見つけることがポイントです。

Excel 365を利用している場合はREGEXEXTRACT関数で簡単に抽出でき、古いExcelではPower QueryやVBAを利用することで大量データにも対応できます。

1万件以上の商品データを扱う場合でも、自動抽出の仕組みを作成しておけば、手作業による入力ミスを減らし、効率的にデータ整理を行えます。