LWP | 記事G001 商品コードを分けて、元の文字列まで確かめる
図解1|商品コードを、意味のある列へ

原文を残し、分割した列と、元へ戻せたかを確かめる列を並べます。
解説1|一つの文字列に入っている三つの項目
「JP-A103-RED」は一つのセルに入っていますが、中には地域・商品・色という三つの項目があります。この教材では、半角ハイフンを項目の境目と決めています。一行が一件の商品コードで、JPやTWは架空の地域ラベルです。実在する商品体系を示すものではありません。
コードの構造を読む
原文の部分 |
この教材での意味 |
分割後の置き場所 |
|---|---|---|
JP |
地域 |
B列 |
A103 |
商品 |
C列 |
RED |
色 |
D列 |
二つの半角ハイフン |
項目を区切る境界 |
値から外し、再結合で戻す |
地域ごとに並べたいとき、コード全体だけを見ていては商品や色も一緒に扱うことになります。項目を列へ分けると、「同じ列には同じ種類の値がある」という表の構造を作れます。ここで大切なのは、単に文字を細かくすることではなく、地域・商品・色の位置を全行でそろえることです。
分割と確認を同じ行へ置く
列 |
入れるもの |
その列で確かめること |
|---|---|---|
A |
原文コード |
変換前の情報を保持する |
B〜D |
地域・商品・色 |
項目の位置がそろったか |
E |
三項目の再結合 |
分割した値を元の形へ戻す |
F |
原文との一致判定 |
変換前後の文字列が一致したか |
G |
商品の文字数 |
商品欄が空になっていないか |
後半では、B〜D列をハイフンで結び直し、A列と比較します。変換する列と検査する列を分けると、問題があったときに元の情報へ戻れます。A列を分割結果で上書きすると、どの文字が元からあったのかを比べにくくなります。
読むポイント|見た目の整列と内容の充足を分ける
4件のうち「TW--BLUE」は中央の商品が欠けています。これも地域・商品・色の三つの位置を持つコードとして扱います。空の商品を消してBLUEを商品列へ詰めると、列の意味が変わってしまいます。位置を保って分けることと、商品が有効かを調べることは別の確認です。この二つを区別しながら、同じ4件を最後まで使います。
図解2|原文と結果の置き場所を決める

A列に4件の原文を残し、B〜G列へ計算結果を置く準備をします。
解説2|見出しとデータを正しいセルへ入れる
空のシートを「コード整理」と名付けます。1行目は列の意味を示す見出し、2〜5行目が4件のデータです。入力する原文と、これから数式で作る結果を分けます。表の上端のA列・B列などと左端の行番号は位置を表す枠で、セルへ貼り付けるデータには含めません。
入力例|コード整理のA1:D5
行 |
A列 |
B列 |
C列 |
D列 |
|---|---|---|---|---|
1 |
原文コード |
地域 |
商品 |
色 |
2 |
JP-A103-RED |
|||
3 |
TW-B205-BLUE |
|||
4 |
JP-A108-GREEN |
|||
5 |
TW--BLUE |
入力するのは見出しとA2:A5の原文です。B2:D5は空けておきます。特にA5は半角ハイフンが二つ連続する「TW--BLUE」です。中央にスペースや「空欄」という文字を入れないでください。後の空要素の比較は、この入力を前提にしています。
入力例|同じシートのE1:G5
行 |
E列 |
F列 |
G列 |
|---|---|---|---|
1 |
再結合 |
原文一致 |
商品文字数 |
2 |
|||
3 |
|||
4 |
|||
5 |
右側には三つの見出しだけを置きます。紙面では二つの表に分けていますが、シート上ではA〜G列が横に連なっています。後の説明もE列・F列・G列という実際の位置を使うので、右側の表を別のシートのA列から入力し直さないでください。
数式を入れる場所を先に確認する
B列のSPLITは、一つの式からB〜D列の三セルへ結果を返します。E・F・G列はそれぞれ別の式で一つの値を返します。B2:G5へ先に図の完成値を手入力すると、式が広がる場所を塞いだり、計算結果と入力値が混ざったりします。
読むポイント|行番号と原文をそろえる
A2がJP-A103-RED、A5がTW--BLUEになっていることを確認します。式はA2から始めて下へコピーするため、一行ずれると調べるコードもずれます。今回は並べ替えず、原文を残したまま、分割・文字数・再結合・照合を順に作ります。入力が違っていた場合は、まずA列を直してから結果を見てください。
図解3|SPLITの四つの引数を読む

同じFALSEでも、第3引数は区切りの扱い、第4引数は空要素の扱いです。
解説3|何を、どこで、どのように分けるのか
SPLITは、一つの文字列を区切りで分け、同じ行の複数セルへ返す関数です。今回の式では、原文と区切りに加え、区切り文字の扱いと空要素の扱いを明示します。二つのFALSEを一まとまりの決まり文句として覚えず、それぞれの役割へ分けて読みます。
B2に入力する式
=SPLIT(A2,"-",FALSE,FALSE)関数の役割|四つの指定
引数の順番 |
今回渡すもの |
指定していること |
|---|---|---|
第1:テキスト |
A2 |
分割する原文を読む |
第2:区切り文字 |
"-" |
半角ハイフンを境目にする |
第3:各文字での分割 |
FALSE |
区切りを一つの文字列として扱う |
第4:空のテキストを削除 |
FALSE |
空の要素も位置として残す |
第2引数の引用符は、ハイフンを文字列として渡すための記号です。式の中では半角の二重引用符を使います。FALSEは真偽値なので引用符では囲みません。関数に渡す値の種類を区別すると、式を読み替えやすくなります。
今回は区切りが一文字なので、第3引数がTRUEでも分ける位置は同じです。複数文字の区切りを使う場合には意味が変わります。一方、第4引数は「TW--BLUE」の中央を残すために必要です。省略時は両方ともTRUEなので、何を省略したかが結果へ影響します。仕様:SPLIT
計算結果|B2:D2へ横に広がる
行 |
B列 |
C列 |
D列 |
|---|---|---|---|
2 |
JP |
A103 |
RED |
入力する式はB2の一つだけです。ハイフンそのものは取り除かれ、前・中央・後ろの三部分がB2・C2・D2へ入ります。原文A2は書き換わりません。三つの結果は独立して入力した値ではなく、B2の一つの式から返されたものです。
読むポイント|返す値の形まで読む
一つの値を返す関数と異なり、SPLITは横に複数の値を返します。そのため、入力セルだけでなく右側の空きも必要です。式が正しくても、結果を置く場所に既存の値があれば展開できません。引数の意味と、戻り値が占める範囲をセットで確かめます。
図解4|式は左端、結果は横へ広がる

B列の式を下へコピーし、それぞれの行のA列を分けます。
解説4|コピーで変わる参照と、広がる結果
B2が参照しているA2は、行や列を固定していない相対参照です。B2の式をB3へコピーすると、参照先も一行下のA3になります。この動きを使って、各行の原文を同じ方法で分割します。A2だけを固定して全行で読み続けると、4件の違いが結果へ反映されません。
コピー先と参照先の対応
式を置くセル |
読む原文 |
結果が広がる範囲 |
|---|---|---|
B2 |
A2 |
B2:D2 |
B3 |
A3 |
B3:D3 |
B4 |
A4 |
B4:D4 |
B5 |
A5 |
B5:D5 |
B2をコピーし、B3:B5を選んで貼り付けます。コピーするのは左端の式です。C列やD列へ同じ式を横にコピーする必要はありません。横へコピーすると参照先が変わり、既に広がっている結果とも重なるため、今回の配置では使いません。
計算結果|コード整理のB1:D5
行 |
B列 |
C列 |
D列 |
|---|---|---|---|
1 |
地域 |
商品 |
色 |
2 |
JP |
A103 |
RED |
3 |
TW |
B205 |
BLUE |
4 |
JP |
A108 |
GREEN |
5 |
TW |
BLUE |
5行目のC列は空の商品です。表の空欄は値がないことを表し、「空欄」という文字を入力する意味ではありません。B5から返される結果の中央が空でも、D5のBLUEは色の列に残っています。見出しと値の種類が4行とも対応しているかを確認してください。
展開できないときに調べる場所
C2:D5に値や式が残っていないか、結合セルがないかを調べます。必要な内容は別の場所へ保管してから展開先を空けます。見た目が空でも別の式が入っている場合があるため、結果を置く範囲の内容を確認します。エラー表示を消すために、式を何度も貼り直すだけでは解決しません。
読むポイント|原文と計算結果の役割を保つ
分割された商品や色を修正したくなったら、先に原文A列が正しいかを見ます。C列やD列の結果へ直接入力すると、一つの式から広がった結果の一部を書き換えることになります。元の情報を直すのか、別の補正列を作るのかを区別してください。今回は原文を保持し、結果に欠けた値があることを確認する練習です。
図解5|空欄を消すと、列の意味がずれる

空の商品位置を残すことで、BLUEが商品列へ詰まるのを防ぎます。
解説5|空の要素も、項目の位置を持っている
「TW--BLUE」には、ハイフンが二つ続く場所があります。最初の境目の後、次の境目までに文字がないため、中央の商品は空です。ナマズさんの「空欄を消したほうがきれいでは」という疑問を、列の意味から考えます。地域・商品・色の三列では、空いた位置を詰めることが正しい整理とは限りません。
二つの式が返す要素を比べる
A5を分ける指定 |
返す要素の順 |
商品列に見える値 |
|---|---|---|
=SPLIT(A5,"-") |
TW、BLUE |
BLUEへずれる |
=SPLIT(A5,"-",FALSE,FALSE) |
TW、空の要素、BLUE |
商品は空のまま |
省略形では第4引数がTRUEとなり、空の要素が除かれます。二つの値を地域・商品・色の見出しの下に置くと、二番目のBLUEが商品列へ入ります。これは色の文字列を商品として解釈させる配置です。値が表示されていることだけでは、正しい分割とは判断できません。
空の位置を残すと確認できること
確認箇所 |
今回の状態 |
そこから分かること |
|---|---|---|
B5 |
TW |
地域は取り出せた |
C5 |
空の要素 |
商品の位置に文字がなかった |
D5 |
BLUE |
色の位置が保たれた |
第4引数のFALSEは、欠けた商品を補う指定ではありません。もともと文字がないことを、元の位置のまま表へ移す指定です。この状態ならC列を点検することで商品の欠損を探せます。空欄を別の値で埋めてしまうと、欠損の事実と補った情報を区別しにくくなります。
比較を試すときの範囲
二つの結果を実際に見比べる場合は、練習用の別シートへA5の原文をコピーして試します。本表のB5の式を省略形へ変えたまま、後続の検査へ進まないでください。以後の図解6〜10は、空の要素を残す式と、最初の4件を使います。
読むポイント|空欄を保持する理由を言葉にする
必要なのは「空欄だから全部残す」という一般則ではなく、三つの項目と位置の対応です。単語だけを取り出す別の目的なら、空要素を除く方法が適する場合もあります。今回のように項目を列へ対応させる表では、空の位置を残すことが後の検査につながります。
図解6|文字数で切るか、区切りで分けるか

固定した位置を使う方法と、ハイフンの位置を使う方法を比べます。
解説6|式が頼りにしている前提を確かめる
地域が必ず2文字、商品が必ず4文字なら、文字の位置を指定して取り出せます。LEFTは左端から指定文字数、MIDは開始位置から指定文字数を取り出します。SPLITは境界を探しますが、LEFT・MIDは位置と長さを使います。どちらが短い式かより、データの規則に合うかが選択の基準です。
J2に入力する比較用の式
=LEFT(A2,2)K2に入力する比較用の式
=MID(A2,4,4)関数の役割|文字位置は左端を1と数える
式の指定 |
今回の意味 |
JP-A103-REDでの結果 |
|---|---|---|
LEFTのA2、2 |
原文の左から2文字 |
JP |
MIDのA2、4、4 |
原文の4文字目から4文字 |
A103 |
原文は1文字目J、2文字目P、3文字目ハイフン、4文字目Aです。この並びだからMIDの開始位置4が商品先頭と一致します。開始位置を3にするとハイフンから取り出してしまいます。結果だけを暗記せず、何文字目が何を表すかを原文へ戻って数えます。仕様:LEFT/MID
地域を三文字にした場合の比較
原文A2 |
J2:LEFT |
K2:MID |
B2:D2:SPLIT |
|---|---|---|---|
JP-A103-RED |
JP |
A103 |
JP・A103・RED |
JPN-A103-RED |
JP |
-A10 |
JPN・A103・RED |
A2を一時的にJPN-A103-REDへ変えると、商品は5文字目から始まります。固定位置のMIDは4文字目から切るため「-A10」になります。SPLITはハイフンの場所を使うので、地域の長さが変わっても三項目を分けられます。
読むポイント|変えた原文を戻してから先へ進む
この比較が終わったら、A2をJP-A103-REDへ戻します。J2・K2は比較用なので、後の再結合や照合の式から参照しません。固定長のコードなら位置で切る方法が使えますが、長さが変わるコードには前提の点検が必要です。区切り方式も、項目の内部に同じ区切りが入らないことを確かめて使います。
図解7|LENで、欠けた商品を見つける

文字数で空の商品を見つけ、商品の実在とは別の確認として扱います。
解説7|数えているのは商品ではなく文字の数
分割後のC列には商品の部分が入っています。LENは、その文字列の長さを数値で返します。商品が欠けた行を見つけるため、G列へ文字数を出します。商品マスターを調べる関数ではないので、4という結果だけで正しい商品と判定することはできません。
G2に入力し、G5までコピーする式
=LEN(C2)計算結果|A列・C列・G列の対応
行 |
A列 |
C列 |
G列 |
|---|---|---|---|
1 |
原文コード |
商品 |
商品文字数 |
2 |
JP-A103-RED |
A103 |
4 |
3 |
TW-B205-BLUE |
B205 |
4 |
4 |
JP-A108-GREEN |
A108 |
4 |
5 |
TW--BLUE |
0 |
B・D・E・F列は紙面で省略しています。G2の参照C2は、下へコピーするとC3・C4・C5へ変わります。G5の0は、SPLITが商品欄を空の要素として残した結果です。図解6で原文を変更した場合は、元へ戻っていることも確かめます。
同じ文字数でも意味は違う
商品欄の例 |
LENの値 |
次に確認すること |
|---|---|---|
A103 |
4 |
商品台帳にあるコードか |
???? |
4 |
4文字でも有効とは限らない |
空の文字列 |
0 |
原文で商品が欠けていないか |
半角スペース一つ |
1 |
空白を値として含んでいないか |
LENは空白も数えます。見た目が空のセルと、長さ0の文字列を同じだと思い込まないことが大切です。今回はC5へスペースを入れず、SPLITの結果をそのまま使っているので0になります。上の比較例は説明用で、本表へ追加する値ではありません。仕様:LEN
読むポイント|点検の手掛かりと最終判定を分ける
この教材では商品部分を4文字と決めています。そのため0や4以外は、原文を調べるきっかけになります。ただし、4文字でも別の商品、打ち間違い、仮の記号かもしれません。文字数の確認と商品台帳との照合を組み合わせると、何を確認できたのかが明確になります。数字だけを直して4に合わせず、欠けた情報の出所を確認してください。
図解8|JOINで、一つのコードへ戻す

地域・商品・色の順に結び、空の商品位置も含めて元の形へ戻します。
解説8|順番と区切りを保って結び直す
JOINは、指定した区切りを値の間へ入れ、一つの文字列へ結合します。今回はB〜D列の分割結果を、地域・商品・色の順に半角ハイフンで結びます。E列は分割の結果から作り直す列です。A列をそのまま写すだけでは、分割で情報が変わらなかったかを調べられません。
E2に入力し、E5までコピーする式
=JOIN("-",B2:D2)関数の役割|結合する範囲を読む
第1引数の「"-"」は値の間に置く文字、第2引数のB2:D2は結ぶ三つの値です。この範囲を左から順に読んで一つの文字列を返します。三つの位置をつなぐため、位置の間には区切りが二つ入ります。下へコピーすると範囲全体がB3:D3、B4:D4、B5:D5へ動きます。仕様:JOIN
計算結果|B1:E5
行 |
B列 |
C列 |
D列 |
E列 |
|---|---|---|---|---|
1 |
地域 |
商品 |
色 |
再結合 |
2 |
JP |
A103 |
RED |
JP-A103-RED |
3 |
TW |
B205 |
BLUE |
TW-B205-BLUE |
4 |
JP |
A108 |
GREEN |
JP-A108-GREEN |
5 |
TW |
BLUE |
TW--BLUE |
5行目では、TWの後に区切り、空の商品の後にも区切りを入れます。中央に文字がないので、二つのハイフンが続く形になります。空の位置を保って分割したことが、再結合で元の形へ戻すためにも役立っています。
結ぶ列や区切りを変えた場合
結合条件 |
変わること |
|---|---|
B:Dを地域・商品・色の順に結ぶ |
原文と同じ順番になる |
地域・色・商品の順に結ぶ |
値が同じでもコードの意味が変わる |
区切りを空文字列にする |
境界のハイフンが戻らない |
読むポイント|再結合だけで終えない
E列に見慣れた形のコードが出ても、細かな文字の違いを目視だけで見逃すことがあります。次の解説9でA列とE列をEXACTで比べます。JOINは三つの値を結ぶ処理、EXACTは二つの文字列を照合する処理です。結果を作る段階と確認する段階を分けると、同じ行の中で変換を追えます。
図解9|EXACTで、原文と照合する

原文一致のTRUEと、商品文字数0は、同時に成立します。
解説9|一致したのは何と何かを説明する
EXACTは二つの文字列を比べ、一致すればTRUE、一致しなければFALSEを返します。今回はA列の原文とE列の再結合を比較します。式が答えるのは「変換前後で同じ文字列か」という問いであり、「商品情報が揃ったか」という問いではありません。
F2に入力し、F5までコピーする式
=EXACT(A2,E2)第1引数に元の文字列、第2引数に結び直した文字列を渡します。下へコピーすると、A3とE3というように同じ行の二セルを比較します。片側だけを固定すると別の行を比べることになるため、この表では両方とも相対参照のままにします。
計算結果|A列・F列・G列の対応
行 |
A列 |
F列 |
G列 |
|---|---|---|---|
1 |
原文コード |
原文一致 |
商品文字数 |
2 |
JP-A103-RED |
TRUE |
4 |
3 |
TW-B205-BLUE |
TRUE |
4 |
4 |
JP-A108-GREEN |
TRUE |
4 |
5 |
TW--BLUE |
TRUE |
0 |
F5がTRUEなのは、欠けた商品を含む原文を、その欠けた位置も含めて再現できたからです。G5が0なのは商品の文字がないからです。両方とも正しい結果で、見ている観点が違います。TRUEを理由にG列の点検を省かないでください。
J4で試す比較用の式
=EXACT("JP-A103-RED","JP-A103-red")この式はFALSEになります。EXACTは英字の大文字と小文字、空白の違いも区別します。A列を書き換えず、文字列を直接渡す小さな例で比較条件を確かめられます。試すセルJ4は本表の計算に使いません。仕様:EXACT
読むポイント|TRUEの意味を広げすぎない
FALSEが出たら、その行のA・B〜D・Eを順に見ます。区切り、要素の位置、再結合の順番、空白や大小文字を確認します。一方、TRUEでも原文自体の誤りは残ります。照合結果の見出しを「正しい商品」ではなく「原文一致」とすることで、今回の式が保証する範囲を読み手へ正確に伝えられます。
図解10|自分の表では、列の意味から決める

原文・区切り・展開先・確認条件を、一組の設計として置き換えます。
解説10|自分のコード体系へ対応させる
今回の4件で確認したのは、地域・商品・色がこの順で並び、各項目の内部には半角ハイフンを含まないコードです。自分の表へ使うときは、先に一行の意味、項目の数と順番、区切りの規則を確認します。商品名自体にハイフンが含まれる場合には、この三列の分け方をそのまま使えません。
置き換える場所を対応させる
対応させるもの |
今回の位置 |
変更するときの確認 |
|---|---|---|
原文 |
A2:A5 |
元の文字列を別列に保持する |
分割の開始セル |
B2 |
必要な項目数だけ右側を空ける |
再結合する範囲 |
B2:D2 |
項目の順番と区切りをそろえる |
原文との照合 |
A2とE2 |
同じ行の変換前後を比べる |
欠損の手掛かり |
C列のLEN |
対象項目と期待文字数を決める |
行を追加したときの確認
6行目へ原文を追加したら、B6へ分割式、E6へ再結合、F6へ照合、G6へ文字数の式をコピーします。C6・D6はB6から広がる場所として空けます。原文だけ追加して、最後の行に検査式がない状態を残さないようにします。
正常なコード、中央の商品が欠けたコード、地域の長さが違うコードなど、期待結果を先に言える少数の行で試します。結果が違った場合は、式の誤りと、コードの規則が前提と違う場合を切り分けます。入力データの意味を確認せず、式だけを複雑にして合わせないことが大切です。
読むポイント|追加要件は別に確かめる
原文全体が空の行、項目数が異なる行、数字だけのコードで先頭の0を残す要件は、この4件の結果だけで判断できません。必要な入力形式と期待結果を決めて確認します。商品台帳との照合や必須項目の確認も別の役割です。分割・文字数・再結合・原文一致を分けた今回の表を土台に、何を追加して確かめるかを明確にできます。
出典
『ちゃんとわかる Googleスプレッドシート』V044_261006、第9章9.2節の9.2.1(LEFT・MID)、9.2.2(SPLIT・JOIN)、9.2.4(LEN)、9.2.6(EXACT)の一部を、独立した商品コード例へ再構成。関数仕様は各解説内のGoogle公式ヘルプを参照。
利用について
Copyright © 2026 LWP 山中 一弘
本資料は、出典を明記いただければ、商用・非商用を問わず、ご自由に複製・改変・再配布していただけます。なお、著作権表示は改変せず、そのまま記載してご利用くださいますようお願いいたします。
