数値の入った文字列を数値と文字に分けるには？
Excelで「400ABC」のように数値と文字列が混在するセルから、数値部分（400）と文字部分（ABC）を分ける方法を解説します。エラーを防ぎ、正しく動作する数式を使用します。
「数値+文字」と「文字+数値」の2つのパターンについて解説します。
例：「数値+文字」の数値部分と文字部分を分ける
A列に元のデータを入力し、B列に数値部分、C列に文字部分を表示します。
データ例
| A | B | C | |
|---|---|---|---|
| 1 | 元のデータ | 数値部分 | 文字部分 |
| 2 | 400ABC | =IFERROR(LOOKUP(9.99999999999999E+307,–LEFT(A2, ROW(INDIRECT(“1:”&LEN(A2))))), “”) | =IFERROR(MID(A2, LEN(B2)+1, LEN(A2)-LEN(B2)), “”) |
| 3 | 123DEF | =IFERROR(LOOKUP(9.99999999999999E+307,–LEFT(A3, ROW(INDIRECT(“1:”&LEN(A3))))), “”) | =IFERROR(MID(A3, LEN(B3)+1, LEN(A3)-LEN(B3)), “”) |
数式の解説
- 数値部分（B列）: =IFERROR(LOOKUP(9.99999999999999E+307,–LEFT(A2, ROW(INDIRECT(“1:”&LEN(A2))))), “”)
- ROW(INDIRECT(“1:”&LEN(A2))): A2の長さに基づいて、1から文字列の長さまでの連続した行番号を生成します。
- LEFT(A2, …): A2の左端から指定した文字数を切り出します。
- –: 切り出した文字列を数値に変換します。
- LOOKUP(9.999…): 最大の数値を見つけます。これが数値部分となります。
- IFERROR(…, “”): エラーが発生した場合は空白を返します。
- 文字部分（C列）: =IFERROR(MID(A2, LEN(B2)+1, LEN(A2)-LEN(B2)), “”)
- LEN(B2): B列（数値部分）の文字数を取得します。
- MID(A2, LEN(B2)+1, …): A2の数値部分の直後から文字列部分を切り出します。
- IFERROR(…, “”): エラーが発生した場合は空白を返します。
結果
以下のように、数値部分と文字部分が正しく分離されます。
| A | B | C | |
|---|---|---|---|
| 1 | 元のデータ | 数値部分 | 文字部分 |
| 2 | 400ABC | 400 | ABC |
| 3 | 123DEF | 123 | DEF |
注意点
- この数式は配列計算を使用しているため、Excelのバージョンによっては「Ctrl + Shift + Enter」で確定する必要があります。
- エラー処理を追加しているため、不正なデータが含まれている場合でも空白として処理されます。
例：「文字+数値」の文字部分と数値部分を分ける
以下の例では、A列に元のデータを入力し、B列に文字部分、C列に数値部分を表示します。
データ例
| A | B | C | |
|---|---|---|---|
| 1 | 元のデータ | 文字部分 | 数値部分 |
| 2 | ABC400 | =LEFT(A2, MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A2&”0123456789″))-1) | =–MID(A2, LEN(B2)+1, LEN(A2)-LEN(B2)) |
| 3 | DEF123 | =LEFT(A3, MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A3&”0123456789″))-1) | =–MID(A3, LEN(B3)+1, LEN(A3)-LEN(B3)) |
数式の解説
- 文字部分（B列）: =LEFT(A2, MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A2&”0123456789″))-1)
- FIND({0,1,…,9}, A2&”0123456789″): A2内で最初に出現する数字の位置を取得します。
- MIN(…): 最初に見つかった数字の位置を取得します。
- LEFT(A2, …-1): A2の左端から最初の数字の手前までを切り出します。
- 数値部分（C列）: =–MID(A2, LEN(B2)+1, LEN(A2)-LEN(B2))
- LEN(B2): B列（文字部分）の文字数を取得します。
- MID(A2, LEN(B2)+1, …): A2の文字部分の次から数値部分を切り出します。
- –: 抽出した文字列を数値に変換します。
結果
以下のように、文字部分と数値部分が分けられます。
| A | B | C | |
|---|---|---|---|
| 1 | 元のデータ | 文字部分 | 数値部分 |
| 2 | ABC400 | ABC | 400 |
| 3 | DEF123 | DEF | 123 |
注意点
- 数値部分が先頭に来ている場合や混在形式（例：「400ABC123」など）では、この数式はそのままでは機能しません。
- エラーが発生する場合には、IFERROR関数を活用してエラー処理を追加してください。
まとめ
Excelで文字+数値の形式を分離するには、FIND関数やLEFT、MID関数、LOOKUP関数やMID関数を組み合わせた方法が便利です。この方法を活用すれば、データ整理や分析が効率化されます。
使用した関数について
IFERROR関数でエラー時の値を指定する方法とVLOOKUPとの組み合わせ方についてわかりやすく解説
IFERROR関数についてIFERRORの概要エラー時の値を指定Excel関数=IFERROR( 値, エラー時の値 )概要 指定した値がエラーの場合に、代替の値を返します。エラーでない場合はその値を返します。 #DIV/0! や #VALUE! などのエラーを処理できます。 数式のエラーによる表示崩れを防ぐために役立...
programming-cafe.com
INDIRECT関数で数値かどうかを判定する方法についてわかりやすく解説
INDIRECT関数についてINDIRECTの概要文字列で指定された参照を返すExcel関数=INDIRECT( 参照 )概要 INDIRECT関数は、指定された文字列をセル参照に変換し、そのセルの値を返します。間接的にセルや範囲を参照するため、動的な範囲指定に便利です。 INDIRECT関数は、セル参照を動的に扱いた...
programming-cafe.com
LEFT関数で指定した数の文字列を先頭から取得する方法と日付処理やRIGHTと組合せ方法についてわかりやすく解説
LEFT関数とLEFTB関数についてLEFTとLEFTBの概要文字列を先頭から取り出すExcel関数=LEFT( 文字列 , 文字数 )=LEFTB( 文字列 , 文字数 )概要 指定した文字数分の文字を先頭(左側)から取り出す 文字列の先頭から、文字数分の位置までの文字列を取り出す 末尾から取り出す関数はRIGHTで...
programming-cafe.com
LEN関数で文字数をカウントする方法と複数セルの文字数取得や空白処理についてわかりやすく解説
LEN関数とLENB関数についてLENの概要文字数をカウントExcel関数=LEN( 文字列 )=LENB( 文字列 )概要 文字数をカウントする関数で、LENBは全角文字(2バイト文字)を2と判定します 半角カナでｶﾞﾀﾞﾊﾞﾊﾟなどは、濁点を1文字として扱うため、ﾀﾞはLENであっても2文字とカウントされる。ﾀﾞ(...
programming-cafe.com
LOOKUP関数で検索値に一致するデータを表示する方法についてわかりやすく解説
LOOKUP関数についてLOOKUPの概要検索値に一致するデータを取得Excel関数=LOOKUP( 検索値 , 検索範囲 , 表示対象範囲 )概要 検索値に一致するデータを検索して表示 検索範囲にある検索値について、表示対象範囲を表示する 検索範囲について昇順で並び替える必要がある 漢字が含まれるときは、並び替え→オ...
programming-cafe.com
MID関数で文字列の取り出す方法と右から取り出す方法や応用テクニックをわかりやすく解説
MID関数とMIDB関数についてMIDとMIDBの概要指定位置から文字列を取り出すExcel関数=MID( 文字列 , 開始位置 , 文字数 )=MIDB( 文字列 , 開始位置 , 文字数 )概要 開始位置から指定した文字数分、文字列を取り出す 指定した文字以降の文字を取り出すには、指定した文字の位置をSEARCHで...
programming-cafe.com
ROW関数で行番号を取得する方法とROWSとの比較やINDEXとの組合せについてわかりやすく解説
ROW関数についてROWの概要行番号を取得するExcel関数=ROW( 対象 )概要 そのセルの行番号を取得する =ROW()と、引数をナシとすると、その関数の位置の行番号が表示される 偶数であるかを判断するISEVEN、奇数であるかを判断するISODD関数と組み合わせて使用されることが多い 行番号(1,2,3,4,5...
programming-cafe.com