「12AA」を数値と文字に分けるには？数値の入った文字列の分離

INDEX

数値の入った文字列を数値と文字に分けるには？
使用した関数について

数値の入った文字列を数値と文字に分けるには？

Excelで「400ABC」や「ABC400」のように、数値と文字列が混在するセルから、数値部分と文字部分をきれいに2列に分けたいことがあります。この記事では、現在のExcelの仕様に合わせ、数式を使わない最も簡単な方法と、最新の関数を使ったスマートな解決策を解説します。

方法1: 【数式不要】「フラッシュフィル」機能で一瞬で分ける

Excelの「フラッシュフィル」を使えば、関数を1つも書くことなく、データのルール性を自動感知して文字と数値を分離してくれます。「数値＋文字」「文字＋数値」のどちらのパターンでも有効です。

操作手順

元のデータ（A列）の隣に、「数値部分（B列）」と「文字部分（C列）」の空欄列を用意します。
2行目のセルに、お手本となるデータを手入力します。
- B2セルに「400」と入力してEnter。
- C2セルに「ABC」と入力してEnter。
入力し終えたB2セルを選択した状態で、キーボードの Ctrl + E を押します（Macは ⌃ + E）。下の行の数値だけが自動で一瞬で抽出されます。
同様に、C2セルを選択した状態で Ctrl + E を押します。文字部分だけが自動で抽出されます。

結果

	A (元データ)	B (数値)	C (文字)
1	元のデータ	数値部分（Ctrl+Eを実行）	文字部分（Ctrl+Eを実行）
2	400ABC	400	ABC
3	123DEF	123	DEF

方法2: 【最新関数】TEXTBEFORE / TEXTAFTER関数で分ける

データが追加・変更された際、リアルタイムに自動計算させたい場合は、モダンExcelで追加された新しいテキスト関数を使うのが最もスマートです。

パターンA：「数値＋文字」の順で並んでいる場合

「400ABC」のように数字が先、文字が後ろにある場合は、アルファベットの先頭を区切り文字として判定します。しかしデータの種類によって文字が変わるため、現在のExcelでは以下のように数式を組み立てます。

数値部分を抜き出す数式（B2セル）：
```
=TEXTBEFORE(A2, {"A","B","C","D","E","F"})
```
※{"A","B"..."F"}部分に、出現する可能性のあるアルファベットや文字の候補を指定することで、その文字より「前（BEFORE）」にある数値をすべて抽出します。
文字部分を抜き出す数式（C2セル）：
```
=TEXTAFTER(A2, B2)
```
※先ほど抽出したB2セルの数値より「後（AFTER）」にある文字列をそっくりそのまま切り出します。

パターンB：「文字＋数値」の順で並んでいる場合

「ABC400」のように文字が先、数字が後ろにある場合は、数字の始まりを基準にします。

文字部分を抜き出す数式（B2セル）：
```
=TEXTBEFORE(A2, {"0","1","2","3","4","5","6","7","8","9"})
```
※いずれかの数字が最初に出現する場所より「前」にある文字列を切り出します。
数値部分を抜き出す数式（C2セル）：
```
=--TEXTAFTER(A2, B2)
```
※文字（B2）より「後」にある数字を切り出します。先頭に「--」を付けることで、切り出した文字としての数字を、Excelが計算可能な正しい「数値データ」に変換できます。

注意点

フラッシュフィル（方法1）は非常に強力ですが、元データを書き換えても結果は自動更新されません。データが頻繁に更新される表では、方法2の関数を用いたアプローチを行ってください。
「400ABC123」のように数値と文字が3階層以上に細かくサンドイッチされている複雑なデータの場合、上記の新関数でも処理しきれないため、その場合はPower Query（パワークエリ）機能の「列の分割（非数字から数字への移行）」を活用してください。

まとめ

Excelで数値と文字列を分離したい場合、まずは一番手軽なショートカットキー「Ctrl + E（フラッシュフィル）」を試すのが実務では最速です。連動性を持たせたい場合は、従来の複雑なLOOKUPの配列数式ではなく、可読性の高いTEXTBEFORE関数やTEXTAFTER関数を用いて、シンプルかつメンテナンスしやすい表を作成しましょう。