序文

目的

Excelの関数を使って、めんどくさい変換を自動で処理して楽をする。

前提条件

A1セルに変換元の値が入っている。
変換元の値には、文法的な間違いはない。
※5桁のコードに4桁しか入ってないとか。

正規表現的なアレ「を」変換する

要件

^(111_)+(222_)+(333_)+$　→　111+222+333+

関数

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"^",""),"(",""),"_",""),")",""),"$","")

仕組み

^　(　)　_　$
の5つの記号を、SUBSTITUTE関数でnull置換する。それだけ。

正規表現的なアレ「に」変換する

要件

111+222+333+　→　^(111_)+(222_)+(333_)+$

関数

="^("&SUBSTITUTE(SUBSTITUTE(A1,"+","_)+(")&"★","(★","$")

仕組み

頭に^(をつけ、SUBSTITUTE関数で+を_)+(に変換して、末尾に$をつける。
だが、そのままだと末尾の+も変換されて、^(111_)+(222_)+(333_)+($になってしまう。最後の(が余分。

そこで、+を_)+(に変換した段階で、いったん末尾に★をくっつける。（★でなくても、変換元の値と競合しなければなんでもいい）
^(111_)+(222_)+(333_)+(★

末尾が(★になっているので、これを$に置換してやれば完成。

コードと名称の分離

要件

　ER123　株式会社ExampleRoom
コードと名称がスペース(半角・全角)でつながっているデータから、コードと名称をそれぞれ取り出す。
スペースの数は不定で、コードの前にもスペースが存在する場合がある。コードは必ず英数5桁。

関数

コードを取り出す
~~=LEFT(SUBSTITUTE(SUBSTITUTE(A1," ",""),"　",""),5)~~
=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),"　",""),CHAR(160),""),5)

名称を取り出す
~~=MID(SUBSTITUTE(SUBSTITUTE(A1," ",""),"　",""),6,LEN(A1))~~
=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A12," ",""),"　",""),CHAR(160),""),6,LEN(A12))

2019/11/09 追記

ノーブレーキング・スペースなるものが含まれていてうまく置換されないことがあることを確認。
それを置換するため、CHAR(160)もnull置換するように修正。
参照：http://searched.jp/?eid=636

仕組み

真ん中の部分で、半角・全角スペースをnull置換。
ER123株式会社ER
コードは左から5文字分なので、LEFT関数で取り出す。
名称は6文字目以降なので、MID関数で始点を6文字目、終点を十分に大きい数字に設定して取り出す。
十分に大きい数字は100でも1000でもいいのだが、「変換元の値の文字数」にしてみた。「変換元の値の文字数」＞「名称の文字数」なので。