Excel で、数字と英字が混在した文字列からそれぞれを別々に取り出す。その2

この記事は自分用の覚書です。
（Windows 10 Pro 64ビット + Excel2016 Office365 Business サブスクリプション　 バージョン1807（ビルド10325.20118）

H.K さん。

「その1」の続きです。

数字が英字（あるいは、英字が数字に）挟まっている場合―― abc456defg とか 987WXY54 ――です。

大事なことなので、「その1」で挙げた要点にもう一度ふれておきます。

• 「仕事でつくるファイルは、できるかぎり誰もが簡単に理解できるようにしておく」ことが大原則。
• そのために、理解がややこしい関数のネストや、我々の職場で理解できる人がほとんどいない配列数式の使用は避ける。
• 結果を得るまでの過程の一つひとつを作業列で処理することで、全体として何をしているのかを誰でも理解しやすくする。
• 紹介したやり方が唯一の正解ではない。
• 関数の理解は、個々人が頑張るしかない。周囲は、その努力への支援を頑張る。「仕事に必要だから覚えてください」と言うだけではだめ。
• 関数のネストや配列数式を、我々の職場で使ってはいけないということではない。周囲の皆が理解できるか、使わなければならない具体的理由がある場合に使う。
• 使うべき理由があって、関数のネストや配列数式を職場で使う場合は、どうなっていて何をしているかをわかりやすく説明したものを用意する。大原則の「できるかぎり誰もが簡単に理解できるようにしておく」というのは、そこまで含んでの話。

数字と英字が互いに挟まっている場合

「元の文字列を部分的に置換して共通の区切りを設ける」ことやその他の基本的な考え方、詳細は「その1」と同じです。

使っている関数も同じです。
CONCAT 関数、SUBSTITUTE 関数、FIND 関数、LEFT 関数と MID 関数です。

ただし、少し異なっている部分があります。
2つ目の区切りの位置を調べる FIND 関数（図ではK列）と、区切りと区切りの間を抜き出す MID 関数（M列）の使い方です。

図の2行目を使って説明します。

2つ目の区切りの位置を FIND 関数で調べる。

FIND 関数の書式は次のようです。

FIND(検索文字列, 対象, [開始位置])

セル K2 の数式は次のようになっています。

=FIND(G2,J2,F2+1)

引数「検索文字列」は、「／」（全角のスラッシュ）です。
「対象」は  6896／hth／56 です。

「開始位置」は、F列の値（ 5 ）に 1 を足しています。
引数「開始位置」を使っているのが「その1」と異なるところです。

値を入れると次のようになります。

=FIND("／","6896／hth／56",5+1)

引数「開始位置」を省くと、FIND 関数は左端からみて最初の「検索文字列」の位置を返します。 6896／hth／56 の場合は、1つ目のスラッシュの位置（5文字目なので 5 が返ってくる。）です。

「開始位置」は「検索文字列」が複数ある場合に使います。FIND 関数が検索を開始する位置を指定します。
6896／hth／56 では、「／」が2つあります。

K列の数式では、「開始位置」で1つ目のスラッシュの位置（ 5 ）に 1 を足しています。最初に現れる「／」より後ろの部分で次に現れる「／」を探すという指定です。
「6文字目から数えると、何文字目に次のスラッシュがあるか」ということです。

2つ目のスラッシュは9文字目なので FIND 関数は 9 を返してきます。

区切りと区切りの間を MID 関数で取り出す。

例では部分的な置換が終わると、6896hth56 が 6896／hth／56 になります。

1つ目の区切りより前の取り出しと、2つ目の区切りより後ろの取り出しは、「その1」で述べたものと同じ考え方をします。

・1つ目の区切りより前を取り出す。（図のL列）
LEFT 関数の引数「文字数」で、1つ目のスラッシュの直前（1つ目のスラッシュの位置 - 1）を指定して 6896 を得ます。

・2つ目の区切りより後ろを取り出す。（図のN列）
MID 関数の引数「開始位置」で、2つ目のスラッシュの直後（2つ目のスラッシュの位置 + 1）を指定します。
引数「文字数」は、ありえないぐらい大きな数（図では100）を入れておきます。それで 56 を得られます。

真ん中にあたる hth も MID 関数で取り出しますが、MID 関数に不慣れだとちょっと考え方がややこしいかと思います。そこの理解は、個々人の頑張りどころです。（図のM列）

MID 関数の書式は次のようです。

MID(文字列, 開始位置, 文字数)

引数「文字列」は、6896／hth／56 です。
「開始位置」は、1つ目のスラッシュの位置 + 1 なので 5 + 1 になります。
（6文字目の h から取り出し始める。）

開始位置から何文字取り出すかを指定する「文字数」は、次のようになります。

2つ目のスラッシュの位置 - 1つ目のスラッシュの位置 - 1

上図に基づいて値を当てはめてみると、次のようになります。

MID("6896／hth／56",5+1,9-5-1)

最初、私は引数「文字数」を、2つ目のスラッシュの位置 - 1 とうっかり考えてしまいました。

6896／hth／56 の場合は、2つ目のスラッシュの位置は 9 文字目なので、それでは 8 文字を取り出す指定になります。
8 文字ということは、1つ目のスラッシュの後ろから残り全部（ 6 文字）である hth／56 がセルに表示されます。

ほしいのは hth の3文字です。

2つの区切りに挟まれた部分が欲しいので、ここで注目すべきは、2つ目のスラッシュの位置を一番右端と捉えて、
6896／hth／
までの固まりです。

「開始位置」が、スラッシュの位置 + 1 なので、取り出しは 6 文字目の h から始まります。
ということは、5 文字目のスラッシュまでは不要といえます。2つ目のスラッシュの位置は左端から 9 文字目ですから不要な部分は引いて 9 - 5 = 4 と考えます。

「文字数」で 6 文字目から 4 文字を取り出す指定をしたということです。
これで、hth／ が残ったことになります。

最後に、右端のスラッシュも不要なのでさらに 1 を引きます。

9 - 5 - 1 となって、答えは 3 です。MID 関数で次のように指定したのと同じです。

MID("6896／hth／56",6,3)

「6 文字目から 3 文字を取り出し」ます。その結果、hth がセルに表示されます。

「その1」から長くなってしまいました。

まとめてしまうと――関数のネストや配列数式は、ぱっと見て理解できる人が私たちの職場にはあまりいないので、具体的な理由なく使わないほうがよいです。作業列で処理するほうが誰にでもわかりやすくてよいです。――ということなんですけどね。

長くなってすみません。