商品コードを分けて、元の文字列まで確かめる

LWP | 記事G001 商品コードを分けて、元の文字列まで確かめる

図解1|商品コードを、意味のある列へ

図1:商品コードを3列へ分解する全体像

原文を残し、分割した列と、元へ戻せたかを確かめる列を並べます。

解説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|原文と結果の置き場所を決める

図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の四つの引数を読む

図3:SPLITの4つの引数

同じ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|式は左端、結果は横へ広がる

図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|空欄を消すと、列の意味がずれる

図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:LEFT・MIDとSPLITの比較

固定した位置を使う方法と、ハイフンの位置を使う方法を比べます。

解説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: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でコードを再結合

地域・商品・色の順に結び、空の商品位置も含めて元の形へ戻します。

解説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で、原文と照合する

図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:自分の表へ適用する確認事項

原文・区切り・展開先・確認条件を、一組の設計として置き換えます。

解説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 山中 一弘

本資料は、出典を明記いただければ、商用・非商用を問わず、ご自由に複製・改変・再配布していただけます。なお、著作権表示は改変せず、そのまま記載してご利用くださいますようお願いいたします。