Excel関数逆引き集 | 可変長文字整形 → LET

Excel

2021.04.022026.01.06

概要
LET 関数の基本イメージ
1. まず「手計算」でやっていることを言葉にする
2. LET の基本構文
例1：可変長の電話番号を LET で整形する
1. やりたいことの整理
2. LET を使ったテンプレート例
例2：可変長の商品コードを LET で前処理してから整形
1. シナリオ
2. LET で分解して考える
例3：長さに応じてフォーマットを変える汎用テンプレート
まとめ：LET を使った可変長文字整形の“考え方”

概要

「桁数も、書き方もバラバラな文字列」を、いつも同じ“キレイな形”にそろえたい場面は多いです。

電話番号
郵便番号
商品コード
名前＋カッコ付きの役職など

ところが、文字の長さやパターンが行ごとに違う 可変長データ を整形しようとすると、式がどんどん長く、読みにくくなりがちです。

そこで力を発揮するのが LET 関数です。
LET は「式の中で変数名を付けて、同じ計算を何度も書かなくて済むようにする」関数なので、複雑な文字列整形を“分割して考える”ことができます。

ここでは、初心者向けに

可変長の文字列を
いったん LET で変数に分解し
読みやすい“分かりやすい式”にしてから整形する

という流れを、具体例とテンプレートで解説します。

LET 関数の基本イメージ

まず「手計算」でやっていることを言葉にする

LET の前に、文字整形の発想をざっくり整理しておきます。

たとえば、「電話番号をキレイに整形したい」と言われたとき、実際には頭の中でこんなことをしています。

まずハイフンを全部取る
数字だけの長さを調べる
長さに応じて「市外局番」「市内局番」などの切り方を変える
LEFT・MID を使ってハイフンを入れ直す

これを、そのまま1本の式に書こうとすると、

SUBSTITUTE(A2,”-“,””)
LEN(その結果)
IF(長さ=10なら…, 長さ=11なら…, …)

が何度も出てきて、かなり読みにくくなります。

ここで LET を使うと、

「ハイフンを取った結果」に名前を付ける
「数字の長さ」に名前を付ける

といったことができて、式を“日本語の手順に近づける”ことができます。

LET の基本構文

LET の形はこうです。

=LET(
  名前1, 値1,
  名前2, 値2,
  …,
  最後に返したい式
)

イメージとしては、

名前1 ＝値1
名前2 ＝値2
…
これらを使って「最後の式」を計算する

という順番で処理されます。

ポイントは、「一度つけた名前を、後ろの式で何度でも使いまわせる」ということです。

例1：可変長の電話番号を LET で整形する

やりたいことの整理

A2 に、いろいろな形の電話番号が入っているとします。

03-1234-5678
0312345678
090-1234-5678

など。
これを「市外局番－市内局番－加入者番号」の形に統一したいとします。

ざっくり言えば、

まずハイフンを全部削除
数字の長さ（10桁 or 11桁）を調べる
桁数に応じて切り方（LEFT/MID/RIGHT）を変える

という流れになります。

LET を使ったテンプレート例

セル A2 に元の電話番号があるとして、B2 に次のように書きます。

=LET(
  raw, A2,
  num, SUBSTITUTE(raw,"-",""),
  n, LEN(num),
  result,
    IF(
      n=10,
      LEFT(num,2) & "-" & MID(num,3,4) & "-" & RIGHT(num,4),
      IF(
        n=11,
        LEFT(num,3) & "-" & MID(num,4,4) & "-" & RIGHT(num,4),
        num
      )
    ),
  result
)

分解して説明します。

raw ＝ A2
元の文字列（生データ）です。

num ＝ SUBSTITUTE(raw,”-“,””)
電話番号からハイフンを全部削除した「数字だけ」の文字列です。

n ＝ LEN(num)
数字の長さ（桁数）。10 なら固定電話、11 なら携帯電話などに使えます。

result ＝ IF(…)
長さ n に応じて、整形後の文字列を作っています。

n=10 のとき（例：0312345678）
LEFT(num,2) & “-” & MID(num,3,4) & “-” & RIGHT(num,4)
→ 03-1234-5678

n=11 のとき（例：09012345678）
LEFT(num,3) & “-” & MID(num,4,4) & “-” & RIGHT(num,4)
→ 090-1234-5678

どちらでもないとき（桁数がおかしいとき）
num（数字だけ）をそのまま返す

最後の result
LET の一番最後に「返したい値」として result を書くことで、この式全体の出力になります。

大事なのは、“途中の処理に名前を付けている”おかげで、

「raw → num → n → result」

という「処理の流れ」が上から順に読めるようになっていることです。

例2：可変長の商品コードを LET で前処理してから整形

シナリオ

A2 に商品コードが入っているとします。

商品A-001-東
商品A001東
商品B-12-西

など、記号の有無や数字桁数がバラバラで、
最終的に「商品名」「番号（3桁）」「エリア」を抜き出したいとします。

例えば、

商品A-001-東 → 商品A / 001 / 東
商品A001東 → 商品A / 001 / 東

のように、まず「番号部分は常に3桁0埋め」にしたい場合を考えます。

LET で分解して考える

本当はかなり条件分岐が増えますが、ここでは考え方をシンプルにするために

「商品名は先頭2文字」
「番号は次の数字だけ」
「エリアは最後の1文字」

と仮定します。

A2 に入っているコードを、
中でいったん「商品名」「数字部分」「エリア」に分解し、
数字部分だけ 3 桁 0 埋めして組み直してみます。

=LET(
  code, A2,
  name, LEFT(code,2),
  area, RIGHT(code,1),
  midPart, MID(code,3,LEN(code)-3),
  numOnly, TEXTJOIN("",TRUE,IF(ISNUMBER(--MID(midPart,SEQUENCE(LEN(midPart)),1)),MID(midPart,SEQUENCE(LEN(midPart)),1),"")),
  num3, TEXT(VALUE(numOnly),"000"),
  result, name & "-" & num3 & "-" & area,
  result
)

やっていることは次の通りです。

code
元のコード全体。

name
LEFT(code,2)
先頭2文字を商品名とみなす。

area
RIGHT(code,1)
末尾1文字をエリアとみなす。

midPart
MID(code,3,LEN(code)-3)
「先頭2文字」と「末尾1文字」を除いた“真ん中部分”。
ここに数字や記号が混ざっている想定です。

numOnly
midPart から数字だけを抜き出している部分。
ここは少し高度ですが、「1文字ずつ見て数字だけを集める」処理をしています。

num3
TEXT(VALUE(numOnly),”000″)
数字だけにしたものを数値化し、3 桁 0 埋めで文字列に変換。

result
name & “-” & num3 & “-” & area
商品名－番号3桁－エリアの形に整形。

最後に result を返す。

中ほどは少し難しめですが、「LET で名前を付けているおかげで手順そのものは読みやすい」という感覚だけ掴んでおけばOKです。

例3：長さに応じてフォーマットを変える汎用テンプレート

「文字数によって書式を変える」というのは、可変長整形の典型パターンです。

例えば A2 に「番号」が入り、

3桁なら「001」形式
4桁なら「00-01」形式
5桁以上なら「そのまま」

のように、長さに応じて整形方法を変えたい場合を考えます。

=LET(
  raw, A2,
  n, LEN(raw),
  result,
    IF(
      n=3,
      TEXT(raw,"000"),
      IF(
        n=4,
        LEFT(raw,2) & "-" & RIGHT(raw,2),
        raw
      )
    ),
  result
)

raw
元の文字列。

n
長さ。

result
長さ n に応じて書式を変えている部分。

最後の result
出力。

「長さによって分岐」が出てくると、IF がネストして読みにくくなりがちですが、
LET で raw と n に名前を付けておくだけでも、ずいぶん読みやすく、保守しやすい式になります。

まとめ：LET を使った可変長文字整形の“考え方”

可変長文字整形で LET を使うときのポイントは、次の 3 ステップです。

A2 などの“生データ”をまず変数名に入れる（raw など）
前処理（記号削除・桁数計算・区切り位置計算）に名前を付ける
最後に「result」として整形済み文字列を作る

式全体を 1 回で書ききろうとすると、
SUBSTITUTE(A2 … LEN(A2 … IF(…
のように途中が何をしているのか分かりにくくなります。

LET を使うと、上から読んでいくだけで

raw → num → n → result

と「処理の流れ」が見えるようになるので、
可変長データの整形や、後で仕様変更が入りそうな複雑なテキスト処理に特に向いています。

問題1

A2 に電話番号（ハイフンあり／なし混在）が入っています。
まずハイフンを削除した文字列を num という名前で保持し、num の長さを n として求めたいです。
LET を使って num と n を定義し、最後に num を返す式を書いてください。

=LET(
  raw, A2,
  num, SUBSTITUTE(raw,"-",""),
  n, LEN(num),
  num
)

問題2

A2 に電話番号が入っています。
LET を使って

num ＝ハイフンを削除した文字列
n ＝ num の長さ
とし、n が 10 のときだけ num を返し、それ以外は空白 “” を返す式を書いてください。

=LET(
  raw, A2,
  num, SUBSTITUTE(raw,"-",""),
  n, LEN(num),
  IF(n=10,num,"")
)

問題3

A2 に任意の文字列が入っています。
LET を使い、
text ＝ A2
len ＝ LEN(text)
という2つの名前を定義し、最後に「len の値」を返す式を書いてください。

=LET(
  text, A2,
  len, LEN(text),
  len
)

問題4

A2 に数値または数値文字列が入っています。
LET を使って raw ＝ A2、n ＝ LEN(raw) とし、
n=3 のときは TEXT(raw,”000″)、それ以外は raw をそのまま返す式を書いてください。
最後に返すのは result という名前にしてください。

=LET(
  raw, A2,
  n, LEN(raw),
  result,
    IF(n=3,TEXT(raw,"000"),raw),
  result
)

問題5

A2 に電話番号が入っています。
LET を使って
raw ＝ A2
num ＝ SUBSTITUTE(raw,”-“,””)
n ＝ LEN(num)
result ＝ n=10 のときは「03-1234-5678」形式（2-4-4）、n=11 のときは「090-1234-5678」形式（3-4-4）、それ以外は num のまま
と定義し、最後に result を返す式を書いてください。

（ヒント：例1のパターンをまとめた形です）

=LET(
  raw, A2,
  num, SUBSTITUTE(raw,"-",""),
  n, LEN(num),
  result,
    IF(
      n=10,
      LEFT(num,2) & "-" & MID(num,3,4) & "-" & RIGHT(num,4),
      IF(
        n=11,
        LEFT(num,3) & "-" & MID(num,4,4) & "-" & RIGHT(num,4),
        num
      )
    ),
  result
)

LET は「可変長文字整形のための箱」として使うと、とても威力を発揮します。
式を“分解して日本語に近づける”感覚で、少しずつ慣れていきましょう。