SUBSTITUTE関数とREPLACE関数に関して

質問

こんにちは。
文字列と数字の間にハイフンを入れ、不要なゼロを消すことが
できる関数を探しています。

例えば・・・
A1のセルに
【ABCD012345】
というデータが入っているとして、
最終的には
【ABCD-12345】
と表示させるようにしたいと考えています。
ABCDと数値の間にハイフンを入れ、数値の頭がゼロの場合は
ゼロを削除して表示させるようにしたいのです。

今私がやっている方法は
B1のセルに
=REPLACE(A1,5,0,"-")
と入れ、
ABCD-012345
と表示させ
C1のセルに
=SUBSTITUTE(B1,"-0","-")
と入れ、不要なゼロをハイフンに置き換えて、
ABCD-12345
と2段階を経て表示させています。

これをもっと簡単にできる方法はありますでしょうか。

よろしくお願いいたします。

回答No.1

hallo-2007 · ベストアンサー

現状の式でうまく行っているのであれば
=SUBSTITUTE(B1,"-0","-")
のB1の部分に　=REPLACE(A1,5,0,"-")　を入れます
=SUBSTITUTE(REPLACE(A1,5,0,"-"),"-0","-")
でうまくいきませんか？

お礼

お教えいただいた方法でやってみたら
うまくいきました！！
ありがとうございます！！
ちなみに、関数と関数を組み合わせる時のルールというのは
何かあるのでしょうか。
この場合、REPLACEを先に持ってきても良いのでしょうか。

初心者すぎて申し訳ありませんが、ご教授いただければ
幸いです。

回答No.8

mt2008

文字列の一番最初に見つかった「0」の1つ前の文字が数字なら「0」を「-」に置換します。
数字が始まる場所が何文字目でも対応できます。
もっといい式も出来そうですが、とりあえずこんな感じでどうで↓

=IF(ISERR(FIND("0",A1)),A1,IF(ISNUMBER(1*MID(A1,FIND("0",A1)-1,1)),A1,REPLACE(A1,FIND("0",A1),1,"-")))

問題として、「0」の前が小数点でも「-」に置換しまいます。

回答No.7

mu2011

一例です。
=LEFT(A1,4)&"-"&MID(A1,5,LEN(A1))*1

回答No.6

CMLT

数字が５文字目から始まる事が前提の数式の一例。

=LEFT(A1,4)&"-"&VALUE(MID(A1,5,LEN(A1)-4))

全体の長さは問いません。５文字目以降を一度数値に変換しています。

回答No.5

tom04

No.2です！
どうも失礼いたしました。
色々回答が出ているみたいなのですが
=REPLACE(A1,5,1,"-")
または
=SUBSTITUTE(A1,RIGHT(LEFT(A1,5),1),"-")
という方法でも出来るみたいです。
しかしあくまで、左から5文字目限定ですが・・・
まだまだやり方は色々ありそうですね。

お礼

ありがとうございます。
=REPLACE(A1,5,1,"-")
上記の数式だと、数値がゼロ以外の場合も消えてしまうので
うまくいきませんでした。
=SUBSTITUTE(A1,RIGHT(LEFT(A1,5),1),"-")
上記の式は、ゼロはすべてハイフンになってしまうので
こちらもうまくいきませんでした。
エクセル関数は奥が深いですね・・・。
難しいです・・・。

回答No.4

gyouda1114

置き換える先頭文字の位置が５で確定しているなら
=REPLACE(D14,5,1,"-")
ではいけませんか

お礼

ありがとうございます。
この方法で試してみましたが、数値の頭がゼロ以外の場合も
消えてしまうのでうまくできませんでした。

回答No.3

koko88okok

=SUBSTITUTE(B1,"-0","-")のB1は、=REPLACE(A1,5,0,"-") と同じものですから、

２つの数式をドッキング(または、代入)させた次の数式をB1セルに、入力すればOKです。
=SUBSTITUTE(REPLACE(A1,5,0,"-"),"-0","-")

お礼

ありがとうございます！
上記の数式でうまくいきました！
助かりましたぁ～！！

回答No.2

tom04

現状のやり方でOKですが・・・
2段階になりますので単純に
=SUBSTITUTE(A1,"0","-")
だけでいいのではないでしょうか？
もしA列の文字列が半角英数であれば
=SUBSTITUTE(A1,0,"-")　でもよろしいかと思います。

ただしセル内に0が複数あってもすべて「-」に置き換わりますのでご注意を！

お礼

ありがとうございます。
そうなんです。複数0がある場合はすべて【-】に
なってしまうんですよね。
そのほかのゼロは生かしておいて数値の頭のゼロだけ消したいのです。。。