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

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

H.K さん。

夏休みもあったし、数字と英字が混在した文字列からそれらを別々に取り出すやり方を考えてみました。

ネット検索すると配列数式を使うやり方が見つかりますが、前に「配列数式は使わない。作業セルを使う。」でふれたように、我々の職場では配列数式は避けて、作業列を使うほうがよいです。

また、関数をネストするやり方もよく見つかりますが、関数のネストも何をしているかの理解を面倒にすることが多いです。

「関数をネストしてはいけない」ということではないです。
ネストするしかない場合もあるでしょうから。
（ セルの数式がエラーかどうかを判定して、エラーだったらエラー表示を非表示にする――エラーを見せない――とか……。）

配列数式と同じで、関数のネストも、周囲の誰でも理解できるか、そうするべき具体的理由がある場合に使ったほうがよいという意味です。

「仕事でつくるファイルは、できるかぎり誰もが簡単に理解できるようにしておく」という大原則を踏まえて、我々の仕事でよく使う関数を使い、作業列で関数のネストをなくして、全体で何をしているかが理解しやすくなるように考えてみました。

なお、以下のやり方が唯一の正解ではないです。仕事でエクセルを使う際にそんなものは無いと私は思います。
（このあと、えらく長くなってしまいました。ごめんなさい。）

課題

• 半角英数字が混在している。数字に続いて英字、英字に続いて数字といった並びになっている。
  ZW123 や 4537defft など。
• 字数は一定ではない。
• ハイフンやスペースなど区切り位置に使える共通の部分がない。

考え方

ZW-123 や 4537-defft といったように区切りとして使える共通のものがあれば、区切りの前後を分けて抜き出すことは難しくありません。FIND 関数、LEFT 関数、MID 関数を使って処理できます。

ということは、作業列を使って「区切りとして使える共通のもの」を付け加えてやればいいんです。
（共通の区切りがあれば、データツールの「区切り位置」機能でも処理できます。今回は前述の関数を使います。我々の職場では必須といえる関数ですし、数式をセルに入れてテンプレートにしておけば、同じ仕事で何度でも使えるツールにすることができます。）

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

何をするのかというと、たとえば元の文字列が 6896hthjd なら左から5文字目の h を、区切りを伴う h に置換します。
6896／hthjd とかに。

共通の区切りができたら、区切りの位置を FIND 関数で求めます。それをもとにして、区切りの直前までは LEFT 関数で、直後から残りを MID 関数で取り出せます。

それぞれの過程を作業列でやっていきます。

1行目はその列が何なのかを表す単なる名札なので、わかりやすいように変えてください。

例では、CONCAT 関数、SUBSTITUTE 関数、FIND 関数、LEFT 関数と MID 関数を使っています。

入力する列は3つです。A列、B列、C列です。

A列に「元の文字列」を入力しています。

B列には「区切りとして使う値」を入力しています。ここでは、全角の／ （スラッシュ）にしました。
"この位置で切り取って分ける" ということが、全角のスラッシュでイメージしやすいかなと思ったもので。

C列に入力するのは、区切りの前後で数字と英字を分けた際に、区切りより後ろの固まりの一文字目（区切りの直後）になる値です。
6896hthjd なら、6896／hthjd にしたいのでスラッシュの直後になる h です。

区切りの直前にある 6 ではなく、なぜ直後の h なのかというと、あとで使う SUBSTITUE 関数に関係していますので、そこでふれます。

なお、C列に入れる値はA列の値に合わせて大文字・小文字を守ってください。FIND 関数は大文字と小文字を区別します。

その区別がない SEARCH 関数を使うのも悪くはないのですが、やはり見たままを入力するほうが、"この値の前で分けている" とイメージしやすいでしょうから、FIND 関数のほうを使いました。

D列よりあとの列は関数の計算結果によって埋めています。

D列で使っている関数について

B列の区切りとC列の値を CONCAT 関数で結合しています。
あとで、A列にある元の文字列を部分的に置換しますが、その置換後である文字列をつくっています。

CONCAT 関数は、Office365サブスクライバーの Excel2016 でないと使えません。
もし、Office365サブスクライバーの Excel2016 ではない Excel で同じことをするなら、 =B2&C2 のように & を使って値を結合するか、CONCATENATE 関数を使ってください。

CONCAT 関数を使った理由は、今回は互換を考える必要がないですし、マイクロソフトが CONCAT 関数の使用を推奨しているからです。
（「今後は CONCAT を使用することを検討する必要があります。これは、CONCATENATE が Excel の将来のバージョンで利用できない可能性があるためです。」　出典：Excel2016 ヘルプ）

E列で使っている関数について

SUBSTITUTE 関数を使っています。
A列の値を部分的に置換することで共通の区切りを付け加えています。

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

SUBSTITUTE(文字列, 検索文字列, 置換文字列, [置換対象])

引数「文字列」が A 列のセル。
「検索文字列」は C 列のセル。
「置換文字列」は D 列のセル。
になります。

「置換対象」は 1 です。
A列のセルにある文字列のなかでC列の値を探しますが、文字列のなかに同じ値が複数ある場合、左端から何番目にあるその値を置換するのかを指定しています。

区切りの直後にあたる値を置換したいので、1 にします。ここを省略すると、同じ「検索文字列」があれば、全部置換されます。
6896hthjd なら、h は5文字目と7文字目にあります。「置換対象」は 1 なので、5文字目（1番目）の h だけが ／h に置換されます。

6896hthjd が 6896／hthjd になります。

同じ値が複数ある場合、区切りの直前の値（ここでは4文字目の 6 ）を 区切りと結合したものに置換（ 6／ ）するには、その値が左端からいくつ目になるかを常に数えないといけません。

課題にあるように「字数は一定ではない」からです。

そこで、区切りの直後にある値を結合するようにしておきます。そうすれば、区切りのところで分けると、直後の値は常に1番目です。
引数「置換対象」を 1 にしておけば、同じ値がいくつあって気にしなくてもよくなります。

REPLACE 関数を使っても同様のことができますが、やってみると、SUBSTITUTE 関数を使う場合より作業列が1つ増えるのがわかりました。もうひと手間かけて REPLACE 関数を使うべき理由がないので、SUBSTITUTE 関数を使うことにしました。

F列で使っている関数について

E列の値のなかにある区切りの位置を、FIND 関数で求めています。

FIND 関数は、「指定された文字列を他の文字列の中で検索し、その文字列が最初に現れる位置を左端から数え、その番号を返します。」（出典：Excel2016 ヘルプ）

6896／hthjd では、スラッシュは5文字目なので 5 になります。

G列とH列で使っている数式

G列とH列で、数字と英字を別々に取り出しています。

G列では LEFT 関数を使って、E列の値のうち区切りより前を取り出しています。
H列では MID 関数を使って、区切りより後ろを全部取り出しています。

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

LEFT(文字列, [文字数])

左側から何文字取り出すかを、引数「文字数」で指定します。
左端から区切りの直前まで取り出すので、「文字数」は「区切りの位置 - 1」になります。

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

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

引数「開始位置」は、元の文字列の何文字目から取り出し始めるかの指定です。
区切りの直後から取り出し始めるので、「区切りの位置 + 1」になります。

引数「文字数」は、「開始位置から何文字取り出すか」の指定ですから、あり得ないくらい大きい値を式のなかに書いてあります。（ここでは 100 ）

これで、数字と英字が混在している文字列をそれぞれに分けることができます。

結果を得るまでに必要な一つひとつの処理を作業列にすることで、各数式はできるだけ単純なものにして、全体として何をしているかを誰にでも理解しやすくしたつもりですが、どうでしょう。

関数のネストや配列数式を使えば、値を入力する必要がなくなるのに……。

もしそう感じるなら、「仕事でつくるファイルは、できるかぎり誰もが簡単に理解できるようにしておく」という大原則を忘れています。

でもまあ確かに、区切りと結合する値を入力する作業が面倒と言えば面倒ですよね。

ネット検索で見つかるような関数のネストや配列数式を使えば、その作業は無くなります。
ですが、我々の職場では何をしているかを理解できる人が限られるファイルになります。

「（誰にでも理解しやすくしておくために）区切りと結合する値を入力する作業」を避けたくて、「（職場で理解できる人が限られる）配列数式や関数のネストを使う」というのは、私に言わせれば、乱暴な仕事の仕方です。

ここで紹介したやり方だと、何をしているかを誰もが簡単に理解しやすいようにしておけます。
「誰にでも理解しやすい」という効用は、区切りと結合する値を入力する少々の面倒を込みにしてもまだ利のほうが多いと、私は判断しました。
（今回はデータ数が何百、何千とあるわけでもないですし。）

元の文字列を見ながら値を入れていくだけなので、勤め始めたばかりの新入社員やパートタイマーの方、アルバイトの方にも無理なくやってもらえます。

上図でいえば、入力してもらうのは実質的にC列だけです。
元の文字列はすでにありますし、B列の「区切り」やD列からH列の数式は、あらかじめ入れておけます。

数式がエラーを起こしますが、C列に値を入力したら消えるエラーなのでそのままでよいと思います。
内部だけで使うものですし、エラーを非表示にしなければならない具体的な理由もありません。

もし、列が増えて見づらければ処理の途中にあたる列を非表示にしたり、必要なレイアウトをつくった別のシートから値を参照すればよいのは、前出のリンク先で述べた通りです。

使っている関数を知らない人もいるから、誰にでも理解しやすいとは言えないのでは――。

職場には各関数の使い方をよく知らない人もいるでしょうが、それらの理解は各人の頑張りどころだと私は思います。

我々の職場では、配列数式を使わなければならないことなんて「無い」と言い切っても言い過ぎではないぐらいですが、関数を使わないで済ませることはできません。

ここで使っている関数は、我々の日々の仕事でしばしば使います。理解しておかなければならない具体的な理由があるといえます。

これらの関数をよく知らない人が職場にいるかもしれませんが、理解できるように各々頑張ってください。仕事に必須ですから――ということです。

もちろん、理解のための支援は、私も含めて周囲が頑張らないといけません。「仕事に必要なんだから理解しておいてよ」と言うだけではだめです。

もし関数のネストや配列数式を使うべき具体的な理由があって使う場合も、大原則は変わりません。
何をしていて、どうなっているのかを、わかりやすく説明したものを用意するなどしてください。

「仕事でつくるファイルは、できるかぎり誰もが簡単に理解できるようにしておく」という大原則は、そこまで含んでの話です。

そもそも、「職場には、使っている関数をよく知らない人もいる」からこそ、配列数式や関数のネストなんてややこしいものを使わないで――作業列で――済ませられるならそのほうがいいでしょう。

やってみた感想

上記では「私、わかってます」風な書き方をしてますが、けっこう悩みました。

「共通の区切りがあれば話は簡単」というのは、割と早めに思いついたのですが、どうやれば区切りを設けられるかがわかりませんでした。
いっときは、本当に配列数式を使わないと、これはさすがに無理かも……という気持ちになりましたし。

1日中こればかりやっているわけにもいかなかったので、結局、「元の文字列を部分的に置換すればよい」と思いつくまでに延べで4日ぐらい、時間にすると4、5時間かかりました。

取っ掛かりさへつかめてしまえば、あとは早かったです。処理の一つひとつを行っているのは、普段から使い慣れている関数ですから。
（画像をつくるほうが時間がかかったぐらいです。）

最終的に、誰にでも任せてやってもらえる仕組みになったと思います。

冒頭のリンク先で、「作業列をどう使うかを考えるのに、それなりの時間はかかるでしょう」と述べたのはこういうことです。アイデアを思いつくまでに時間がかかることがあるんです。

そして、「ややこしい配列数式を頑張って理解して使うことに時間をかけるよりは、作業列をどう使うかを考えるのに時間をかけたほうが、結局は業務全体の効率を上げることにつながる」――という思いも変わっていません。

ちなみに、今回は課題に入っていませんが、数字が英字（あるいは、英字が数字に）挟まっている場合もあるかと思います。abc456defg とか、987WXY54 とか。

そういう場合も、上記と同様の考え方で処理できます。
長くなったので、それについては次の記事で。