エクセル上とVBA上の動きの違いについて

質問

教えてください。
先程質問をして早まって回答を締め切りしてしまったものです・・・。
申し訳ないです・・・。

質問の内容としては、
「１２３４　　１４　　　３５」　のように、
数字と数字の間にスペースが入っています。
スペースの数は一定ではありません。
これを一つのハイフン”－”に変換したいのですが、
どうしたらよいでしょうか？
substituteを使うと、１２３４－－－－１４－－３５になってしまいますし、TRIMを使うと消えてしまいますし・・・。

というものでした。
回答で、SUBSTITUTE(TRIM(A1)," ","-")
を教えていただき、早速エクセルでためしたところ、
結果がOKだったので喜んで締め切ってしまいました・・。

ところが、実際にVBAに
kakou2 = Application.Substitute(Trim(b(x)), " ", "-")
とかいたところ、うまくいきません。。
元データは「ABCD 123456」 で、結果は
「ABCD--123456」になりました。
なぜなんでしょう・・・。

今度からきちんと最後まで確認してから回答を締め切ろうと
反省しております。　どなたか助けてください。。
よろしくお願いします。

回答No.2

bonaron · ベストアンサー

ワークシート関数の TRIM
VBA の Trim の仕様の違いです。
> kakou2 = Application.Substitute(Trim(b(x)), " ", "-")
は、VBA の Trim 関数が実行されてしまいます。
　kakou2 = Application.Substitute(Application.Trim(b(x)), " ", "-")
にしてください。

VB の Trim のヘルプ：
指定した文字列から先頭のスペース (LTrim) または 末尾のスペース (RTrim)、あるいは先頭と末尾の両方のスペース (Trim) を削除した文字列を表すバリアント型 (内部処理形式 String の Variant) の値を返します。

ワークシート関数の TRIM のヘルプ：
文字列に複数のスペースが連続して含まれている場合、単語間のスペースを 1 つずつ残して、不要なスペースをすべて削除します。TRIM 関数は、ほかのアプリケーションから読み込んだテキストに不要なスペースが含まれているときなどに使用します。

お礼

なるほど！！！
VBAのTRIMとワークシート関数のTRIMですか！
詳細説明もありがとうございます。
早速試して確認しました。バッチリでした。

ありがとうございました。

回答No.3

o_chi_chi

b = Worksheets(1).Cells("A1").value
b = Application.Substitute(Application.Trim(b), " ", "-")

回答No.1

n-jun

スペースが半角であると仮定した場合なら、

Sub try()
Dim v, w, st As String

v = Split("ABC 123 456", " ")

For Each w In v
If Len(w) > 0 Then st = st & w & "-"
Next

MsgBox Left(st, Len(st) - 1)

End Sub

とかなら思いつきましたけど。
違っていたらスル～して下さい。