証券コードと​科目コードを​関数で​切り​出す｜LEFT・​MID・​FIND・​TEXTBEFOREで​分ける

想定読者：IB・PE・FP&A や経理の担当者で、社内リストや外部から受け取ったデータの「コード＋名称」列を、株価表や試算表と照合している方
前提知識：セル参照と IF 関数の基本（切り出しに使う関数は本文で説明します）

結論：区切りの​位置を FIND で​求め、​LEFT と MID で​切り、​検算表で​確かめる

「2410 サンプル鉄鋼」のように、証券コードと社名が1つのセルに入った列は、次の3ステップで2列に分けられます。

1. FIND で、区切りのスペースが左から何文字目にあるかを求める。
2. その手前までを LEFT で取り出し、コードとする。
3. 区切りの次から最後までを MID で取り出し、名称とする。最後までの長さは LEN で取る。

切り出した後は、行数・コード長・ユニーク件数・照合一致率の4つを検算表で確かめ、区切りのない行は IFERROR で別列に隔離します。区切り位置やフラッシュフィルで分ける方法はExcelの​財務​データクリーニング​実務に譲ります。フラッシュフィル（見本から残りを自動で埋める機能）は結果が値として固定されるため、更新されるリストには数式が向きます。

証券コードは長く数字4桁でしたが、日本取引所グループ（JPX）の公表によると、2024年1月1日以降に新しく設定する株式のコードには英字が入ります（2桁目か4桁目に使い、例は 987A）。それ以前のコードは変更されません¹。コードは数値とは限らず、切り出した後も文字列として扱う必要があります。

切り出しに​使う​関数と​「文字の位置」

LEFT は左から指定した文字数を返し²、MID は開始位置と文字数を指定して途中から返し³、LEN は文字数を数えます⁴。数える単位は「文字」で、全角も半角も、スペースも1文字です。バイト単位の LEFTB・MIDB・LENB は各ページで非推奨とされているため使いません。

位置を求める FIND は、探す文字列が左から何文字目にあるかを返し、大文字と小文字を区別し、見つからないと#VALUE! と​いう​エラー値になります⁵。桁数が全行で決まっているなら、LEFT や RIGHT（右端から指定した文字数を返す関数）だけで足ります。

図表1は「2410 サンプル鉄鋼」の例です。左から数えて、「2410」が1〜4文字目、スペースが5文字目、「サンプル鉄鋼」が6〜11文字目で、全体は11文字です。

コードは「スペースの位置−1」文字、名称は「スペースの位置＋1」文字目から末尾までです。MID の第3引数に LEN(A2) を渡すのは、名称の長さが行ごとに違うからで、MID は文字数が残りより多いと末尾までを返します（編集部の Excel で確認）。

設例：30行の​「上場会社リスト」を​2列に​分ける

以下は学習用の架空事例です。社名・コードはすべて仮のもので、実在の企業・銘柄コードとは関係ありません。A列の30行の会社リストを、別シートの価格表（コードをキーとする29社分。文字列）と照合します。リストには6つの型が混ざっています（図表2）。

①③の21行は素直に切れます。②は全角スペース、④はコードが全角数字、⑤⑥は半角スペース自体がありません。①には名称にも半角スペースを含む行（「7603 サンプル ホールディングス」）が1行あります。

基本形：FIND→LEFT→MID の​3ステップ

まず手を加えない基本形です。A2に元データを置き、C列に区切りの位置、D列にコード、E列に名称を出します。

C2  =FIND(" ",A2)
D2  =LEFT(A2,C2-1)
E2  =MID(A2,C2+1,LEN(A2))

この基本形を30行に引き下ろすと、①③④の23行に値が入り、②の4行と⑤⑥の3行（計7行）はC列が #VALUE! になります。

LEFT(A2,4) で済ませると30行すべてに値が入り、エラーは出ません。照合すると27行が一致し、不一致は全角数字の2行と、「サンプル運輸」から「サンプル」を取り出した1行です。位置を計算する式なら、区切りのない行がエラーとして表に出ます。

全角が​混ざる​行は、​切る​前に​整える

②で FIND が外れるのは、全角スペースと半角スペースが別の文字だからです。編集部の Excel（Windows 版 16.0）では、半角スペースを探す FIND は「2801　サンプル」で #VALUE! になり、全角スペースを探すと5を返しました。そこでB列で全角スペースを半角に置き換えてから切ります。

④は FIND が成功するぶん厄介です。「２４０２」は切り出せますが、価格表のキー「2402」とは別の文字列なので一致しません。全角を半角に変える ASC 関数⁶をコード部分にかけます。行全体にはかけません。編集部の Excel では ASC(“サンプル”) が「ｻﾝﾌﾟﾙ」を返し、社名まで半角になるからです。

順序は「スペースを揃える→位置を求める→切る→コードだけ ASC」です（式は次節）。

区切りの​ない​行は IFERROR で​隔離し、​件数を​数える

区切りのない行は、IFERROR 関数（エラーのときだけ別の値を返す関数）で FIND のエラーを 0（「区切りなし」の目印）に置き換え、隔離用の列に出します。

B2  =SUBSTITUTE(A2,"　"," ")
C2  =IFERROR(FIND(" ",B2),0)
D2  =IF(C2=0,"",ASC(LEFT(B2,C2-1)))
E2  =IF(C2=0,"",MID(B2,C2+1,LEN(B2)))
F2  =IF(C2=0,"要確認","")

F列の「要確認」を数えれば隔離した行数が分かります。エラーを空白に変えるだけだと行が消えたことに気づけないため、「要確認」で残します。使ってよい場面はIFERROR​関数の​使い方を参照してください。

⑤のハイフン区切りは、区切りを「-」にした別の式で救えますが、「4110-01」のようにハイフンがコードの一部になる列には使えません。⑤⑥は人が元データを確認します。

切り出した​コードは​文字列の​まま​照合キーに​する

切り出したコードは VALUE 関数で数値にせず、文字列のまま持ちます。理由は3つです。

1. 英字入りのコード。編集部の Excel で VALUE(“345A”) は #VALUE! になりました。
2. 先頭ゼロ。VALUE(“0210”) は 210 になり、桁が欠けます。
3. ハイフン入りの科目コード。VALUE(“4110-01”) は日付（4110年1月1日）と解釈され、807188 を返しました（日本語環境の編集部の Excel）。

照合には XLOOKUP（検索値を探し、対応する列の値を返す関数）が使えます。Microsoft の説明では、第4引数 if_not_found は一致しないときに返す値で、XLOOKUP は Excel 2016・2019 では使えません⁷。次の式は公開仕様に沿って書いたもので、編集部の Excel（XLOOKUP 非対応）では実行していません。

H2  =IF(D2="","",XLOOKUP(D2,価格!$A$1:$A$29,価格!$B$1:$B$29,"なし"))

編集部の Excel で実行し、図表3の件数を確かめたのは INDEX/MATCH 版です（使い分けはINDEX/MATCHと​XLOOKUP​実践）。

H2  =IF(D2="","",IFERROR(INDEX(価格!$B$1:$B$29,MATCH(D2,価格!$A$1:$A$29,0)),"なし"))

この完全一致の MATCH では、数値の 2402 は文字列の “2402” と、全角の “２４０２” は半角と一致しませんでした。XLOOKUP でも同じ結果になるかは、手元では確かめていません。

科目コードも同じ式で切れます。「4110-01 売掛金」形式の10行（「0210-01 建物」のように先頭ゼロの行を含む）は、区切りがスペースなので同じ式がそのまま使え、10件とも一致しました。科目コードは勘定科目​マッピングのキーで、試算表からの​モデル化の入口になります。

検算表：​「見た目が揃った」を​検算に​しない

切り出しが全行で働いたかは、見た目では分かりません。図表3の項目を次の式で数えます（データは2〜31行目）。

隔離      =COUNTIF(F2:F31,"要確認")
成功      =ROWS(A2:A31)-隔離
コード長  =COUNTIF(G2:G31,4)          （G2 =IF(D2="","",LEN(D2))）
ユニーク  =SUMPRODUCT((D2:D31<>"")/COUNTIF(D2:D31,D2:D31&""))
一致      =成功-COUNTIF(H2:H31,"なし")

読み方は3点です。第一に、成功＋隔離が行数に一致するか（23＋7＝30、27＋3＝30）。第二に、コード長は整える前も後も成功行すべてが4文字で、全角数字の「２４０２」も4文字のため、長さでは不具合を見つけられません。見つけたのは整える前の一致率 91.3％（21÷23）でした。第三に、ユニーク件数が成功行数と同じなので重複はありません。

隔離の3行は整えた後も残るため、「一致率100.0％」だけを報告せず「未解決」と書き添えます。検算の設計全般は検算​チェックの​設計を参照してください。

Microsoft 365 では TEXTBEFORE／TEXTAFTER に​置き​換えられる

TEXTBEFORE は区切りより前のテキストを、TEXTAFTER は後のテキストを返します（図表1の右列）。区切りが見つからないと既定で #N/A ですが、6番目の引数 if_not_found に値を渡すと代わりにその値を返します⁸。TEXTAFTER も同じ引数構成です⁹。隔離は次のように書けます。

D2  =TEXTBEFORE(B2," ",,,,"要確認")
E2  =TEXTAFTER(B2," ",,,,"要確認")

この2式は公開仕様に沿って書いたもので、編集部の環境では実行できていません。B列の整形と ASC は引き続き必要です。区切りで複数の列に分ける TEXTSPLIT は、1つの式で結果が複数のセルに広がります¹⁰（動的配列。詳細はExcel動的配列関数の​実務）。毎月取り込むデータは、Power Queryで分割する方法もあります。

各ページの「適用対象」は Excel for Microsoft 365 と Excel 2024 で、2021 以前は挙がっていません。社外へ配布するモデルでは、LEFT・FIND・MID・LEN の式と INDEX/MATCH の照合を残しておくと、相手のバージョンに左右されにくくなります。

どの​式を​選ぶか：判断フロー

選択を図表4にまとめました。

実務​チェックリスト

• 切る前に、全角スペース・全角数字・英字の混在がないか、LEN と FIND の結果で確かめた
• 桁数の決め打ち（LEFT(A2,4) など）をせず、FIND で区切りの位置を求めた
• コードは VALUE で数値にせず文字列のまま持ち、照合先のキーも文字列にそろえた
• 成功＋隔離＝行数、ユニーク件数＝成功行数、一致率の3つが検算表で成り立っている
• 社外へ配布するモデルは、LEFT・FIND・MID の式と INDEX/MATCH の照合を残した

よく​ある​質問​（FAQ）

まとめ

コードと名称が混在した列は、FIND で位置を求め、LEFT と MID（長さは LEN）で2列に分けられます。全角スペースは SUBSTITUTE、全角数字はコードだけ ASC で揃えてから切ります。区切りのない行は IFERROR で隔離して件数を数え、行数・ユニーク件数・照合一致率で確かめ、コードは文字列のまま照合キーにします。TEXTBEFORE／TEXTAFTER や XLOOKUP は、配布先のバージョンで使えるかを確かめてから選びます。

脚注

1. JPX「証券コード英文字組入れ」（概要・使用する英文字・適用）と、同「証券コードへの英文字組入れの開始について」（2023年12月13日）。英字は2桁目・4桁目に使用、既存コードは変更されない（R1・R2）。↩
2. Microsoft サポート「LEFT function」。LEFTB は非推奨との記載（R3）。LEFT・MID・RIGHT で文字列の左・中・右を取り出すという一般概念：Wall Street Oasis（R11）。↩
3. Microsoft サポート「MID function」。文字数が残りより多いと末尾まで返す。MIDB は非推奨との記載（R4）。↩
4. Microsoft サポート「LEN function」。スペースも1文字。LENB は非推奨との記載（R5）。↩
5. Microsoft サポート「How to correct a #VALUE! error in FIND/FINDB and SEARCH/SEARCHB functions」。FIND は大文字小文字を区別する（R6）。FIND 本体のページは2026年10月22日時点で「page not found」となり閲覧できなかった。↩
6. Microsoft サポート「ASC function」。2バイト文字セット（DBCS）の言語で、全角（2バイト）文字を半角（1バイト）文字に変える（R7）。↩
7. Microsoft サポート「XLOOKUP function」。if_not_found は有効な一致が見つからないときに返す値（省略時は #N/A）。「XLOOKUP is not available in Excel 2016 and Excel 2019」との注記（R12）。↩
8. Microsoft サポート「TEXTBEFORE function」。if_not_found の既定は #N/A、instance_num は負の値で末尾から数える。適用対象は Microsoft 365・Excel 2024（R8）。↩
9. Microsoft サポート「TEXTAFTER function」。引数は TEXTBEFORE と同じ構成。適用対象も同じ（R9）。↩
10. Microsoft サポート「TEXTSPLIT function」。列・行の区切りでテキストを分割する。適用対象も同じ（R10）。↩

参考文献​（2026年10月確認）

• R1　日本取引所​グループ​証券​コード​協議会​「証券コード英文字組入れ」 jpx.co.jp （概要・使用する英文字・適用。脚注1。閲覧日：2026年10月22日）
• R2　日本取引所​グループ「証券コードへの英文字組入れの開始について」 jpx.co.jp （本文。脚注1。閲覧日：2026年10月22日）
• R3　Microsoft​サポート​「LEFT function」 support.microsoft.com （構文・num_chars の説明・LEFTB 非推奨の注記。脚注2。閲覧日：2026年10月22日）
• R4　Microsoft​サポート​「MID function」 support.microsoft.com （構文・引数・MIDB 非推奨の注記。脚注3。閲覧日：2026年10月22日）
• R5　Microsoft​サポート​「LEN function」 support.microsoft.com （構文・説明・LENB 非推奨の注記。脚注4。閲覧日：2026年10月22日）
• R6　Microsoftサポート「How to correct a #VALUE! error in FIND/FINDB and SEARCH/SEARCHB functions」 support.microsoft.com （3つの原因。脚注5。閲覧日：2026年10月22日）
• R7　Microsoft​サポート​「ASC function」 support.microsoft.com （説明・構文。脚注6。閲覧日：2026年10月22日）
• R8　Microsoft​サポート​「TEXTBEFORE function」 support.microsoft.com （構文・適用対象。脚注8。閲覧日：2026年10月22日）
• R9　Microsoft​サポート​「TEXTAFTER function」 support.microsoft.com （構文・適用対象。脚注9。閲覧日：2026年10月22日）
• R10　Microsoft​サポート​「TEXTSPLIT function」 support.microsoft.com （構文・適用対象。脚注10。閲覧日：2026年10月22日）
• R11　Wall Street Oasis「LEFT Function」（ページ表記の最終更新：2024年12月23日） wallstreetoasis.com （一般概念の参考。脚注2。閲覧日：2026年10月22日）
• R12　Microsoft​サポート​「XLOOKUP function」 support.microsoft.com （構文・if_not_found・Excel 2016／2019 非対応の注記。脚注7。閲覧日：2026年10月22日）

※本記事は教育目的の一般的な解説であり、法務・税務・投資助言ではありません。設例は理解のための学習用の架空事例であり、実在の企業・案件とは関係ありません。