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 回答日時： 2009/01/28 23:18

現状の式でうまく行っているのであれば

=SUBSTITUTE(B1,"-0","-")
のB1の部分に　=REPLACE(A1,5,0,"-")　を入れます
=SUBSTITUTE(REPLACE(A1,5,0,"-"),"-0","-")
でうまくいきませんか？

この回答へのお礼

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

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

お礼日時：2009/01/28 23:22

No.8 回答者： mt2008 回答日時： 2009/01/29 10:52

文字列の一番最初に見つかった「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 回答日時： 2009/01/29 10:08

一例です。

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

No.6 回答者： CMLT 回答日時： 2009/01/29 06:19

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

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

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

No.5 回答者： tom04 回答日時： 2009/01/28 23:52

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),"-")
上記の式は、ゼロはすべてハイフンになってしまうので
こちらもうまくいきませんでした。
エクセル関数は奥が深いですね・・・。
難しいです・・・。

お礼日時：2009/01/29 00:03

No.4 回答者： gyouda1114 回答日時： 2009/01/28 23:39

置き換える先頭文字の位置が５で確定しているなら

=REPLACE(D14,5,1,"-")
ではいけませんか

この回答へのお礼

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

お礼日時：2009/01/28 23:49

No.3 回答者： koko88okok 回答日時： 2009/01/28 23:30

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

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

この回答へのお礼

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

お礼日時：2009/01/28 23:51

No.2 回答者： tom04 回答日時： 2009/01/28 23:25

現状のやり方でOKですが・・・

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

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

この回答へのお礼

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

お礼日時：2009/01/28 23:44