文字の置換のVBA

質問

こんばんは。
下記の文字列の置換をマクロ(VBA)で出来ますでしょうか。
今は関数(SUBSTITUTE)を繰り返して処理しているのですが、１度で出来る方法がありましたら教えて下さい。

100 ABC-23d=3,4　→　100ABC23D3.4

(1)スペース削除
(2)小文字を大文字へ
(3)"-" "=" など数字とアルファベット以外を削除
(4)","→　"." (カンマをドットへ変換）

宜しくお願いします。

回答No.6

sakusaker7 · ベストアンサー

「一度でできる」というのは自分でマクロを組んだ上で
一回の操作でできればいいという話ですか?
#てっきり一命令でという話かと
であるなら、#4の方の回答のようにすればよいと思います。

Function Sample(ByVal vArg As String) As String
Dim oReg
Dim sStr As String
Set oReg = CreateObject("VBScript.RegExp")
oReg.Pattern = "[^0-9A-Za-z.,]+"
oReg.Global = True
sStr = vArg
sStr = oReg.Replace(sStr, "")
Sample = sStr
End Function

このユーザー定義関数を使えばたとえば
100 ABC-23d=3,4 から 100ABC23d3,4
までは変換できます。
あとはこの関数に 小文字→大文字の変換の命令と
カンマ→ドット への命令を付け足せばよいです。

あと、どうせなら範囲選択して呼び出すとその範囲全部に関数を適用するように
マクロを作ればいいと思いますが、そこまでは
面倒見切れないので提案だけ。

お礼

回答有難うございます。
はい、一回の操作で出来れば…と考えてました。
でもNo.6さんや他の方に教えて頂いた内容が良く理解出来ません。。。
本当に難しいですね。
もっと勉強しながら、教えて頂いた内容が出来るようがんばります。
いずれは
>あと、どうせなら範囲選択して呼び出すとその範囲全部に関数を適用するようにマクロを作ればいいと思いますが、
これ、実現したいです ^^

回答No.7

noname#140971

> そのまま使える数式など教えて頂けると嬉しいです。

そのように便利な数式とか関数はないと思いますよ。

[イミディエイト]
? PurgeChar("100 ABC-23d=3,4"," ,-,=,+,/,×,÷")
100ABC23d3,4
? ReplaceChar("100 ABC-23d=3,4", ",->.||、->.")
100 ABC-23d=3.4

PurgeChar関数は、コンマ(,)で区切られた文字の全てを文字列から追放します。
ReplaceChar関数は、’||’で指示された置換を全て実行します。
これらを組み合わせるしかないと思います。

? UCASE(ReplaceChar(PurgeChar("100 ABC-23d=3,4"," ,-,=,+,/,×,÷"), ",->.||、->."))
100ABC23D3.4

追放する文字列等は、

Const 追放文字列=" ,-,=,+,/,×,÷"
Const 置換文字列=",->.||、->."

と定義すべきかと・・・。

UCASE(ReplaceChar(PurgeChar("100 ABC-23d=3,4",追放文字列), 置換文字列))

これが実際のＶＢＡのコード。

種々の手続きを非手続き的に実行したけりゃ、それぞれの処理を担当する関数を作成するが一番。
私は、このように思います、

お礼

回答有難うございます。
そんな便利な数式ありませんよね。。。
最近VBAの勉強始めて、今まで関数など繰り返していた操作が一度に出来るようになって感動しちゃって。
こんな事も出来るかな？？と欲が出てしまいました ^^
皆さんに教えて頂いた内容、まだ良く理解出来てないんですが
もっと勉強して実行できるようがんばります！！

補足

回答者の皆様へ
色々教えて頂き本当に有難うございます。
「出来ました！！」と言うお礼がしたかったのですが、難しくて未だ実行できていません。。。
でも教えて頂いた内容を絶対に出来るようにがんばります。

皆様へポイントを差し上げたかったのですが…今回は2回、回答頂いた方にポイント付けさせて頂きます。
本当に有難うございました。

回答No.5

noname#67980

> そのまま使える数式など教えて頂けると嬉しいです。

例えば

dim strTarget As String

今回の場合数式が、
「100 ABC-23d=3,4」とすると

strTarget = "100 ABC-23d=3,4"

1.文字列を置換するときは、Replace関数と言うものを使います。
スペースを削除すると言うことは、スペースをNull文字に置換すると言うのと同義です。

なので、
※１
Replace(strTarget,Space(1),vbNullString)

Space(1)と言うのは、" "と同じ意味です。
また、vbNullStringは ""と同じ意味です。

同様に、カンマをドットへ変換も、Replace関数が使えます。
なので、
※２
Replace(strTarget,",",".")

でカンマをドットに変換することができます。
※１と※２を組み合わせると※２のstrTargetを※１の式に変換して
※３
Replace(Replace(strTarget,Space(1),vbNullString),",",".")

となります。

2.小文字を大文字に変換する関数は、UCaseという関数を使います。
＃逆に大文字を小文字にしたいときは、LCaseを使います。
※４
Ucase(strTarget)
で小文字が大文字に変換できます。

なので、※３と※４の式を組み合わせると※４のstrTargetを※３の式に変換して

※５
Ucase(Replace(Replace(strTarget,Space(1),vbNullString),",","."))

となります。

上記の式(※５)で、
(1)スペース削除
(2)小文字を大文字へ
(4)","→　"." (カンマをドットへ変換）

の変換は出来ると思いますが、いかがでしょうか？

お礼

回答有難うございます。
難しいです。。。
VBAは最近始めたばかりで、実は "dim"　って何？？レベルなんです。。。
教え頂いた内容を良く読んで、がんばってみます！！

回答No.4

noname#52504

●甲案：VBAで…。

正規表現を使えばもっとスマートにできるのではないかと思いますが、
とりあえず、ぢみ～に回してチェックするアプローチで…。

動作の概要
　引数として与えられた文字列について、
　・カンマ","をピリオド"."に置換
　・小文字のアルファベットを大文字に変換
　・アルファベット，数字，ピリオド以外の文字を削除
　した文字列を返す。

※ユーザー定義関数です。
　ワークシート上で関数として使うことを想定しています。
　　=Sample(A1)

※全角文字はすべて削除されます。
　全角英数字を半角に直して残す場合は、ワークシート上で、
　　=Sample(ASC(A1))
　のようにしてください。
'--------------------↓　ココカラ　↓--------------------
Function Sample(ByVal myStr As String) As String
　'残す文字
　myChr = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789."

　myRtv = ""
　myStr = Replace(myStr, ",", ".")
　myStr = UCase(myStr)　
　For i = 1 To Len(myStr)
　　For j = 1 To Len(myChr)
　　　If Mid(myStr, i, 1) = Mid(myChr, j, 1) Then
　　　　myRtv = myRtv & Mid(myStr, i, 1)
　　　　Exit For
　　　End If
　　Next j
　Next i　
　Sample = myRtv
End Function
'--------------------↑　ココマデ　↑--------------------
Excel2003で（一応）動作確認済

*****************************************************************
●乙案：ワークシート関数で…。

地味というよりほとんど冗談ですが…。

A列1行目以下に元のデータが入っているとします。
元のデータの文字数が全体で20文字以内だとします。

１．B1セルに以下の数式を【配列数式として】入力する

　=REPLACE(SUBSTITUTE(UPPER(ASC(A1)),",","."),MATCH(0,MMULT(TRANSPOSE(ROW($1:$37)),--(MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.",ROW($1:$37),1)=TRANSPOSE(MID(SUBSTITUTE(UPPER(ASC(A1)),",","."),ROW($1:$20),1)))),0),1,"")

通常の数式は、数式バーに打ち込んだ後Enterキーで確定しますが、
配列数式は、CtrlとShiftを押しながらEnterキーを押して確定します。
http://pc.nikkeibp.co.jp/pc21/special/hr/hr1.shtml

２．B1セルをU列まで右方にフィルする
　U列←B列から数えて第20列　

３．B1:U1を下方にフィルする
４．B列～T列を非表示にする

U列に表示される文字列が求める文字列…のハズです。
Excel2003で（一応）動作確認済。

元のデータの文字数が20文字より長い場合には、
数式の$20となっている部分を増やし、
U列よりさらに右方にフィルしてください。

$37は、アルファベット26文字＋数字10文字＋ピリオドで37。
元データの文字数とは関係ありません。

以上ご参考まで。長乱文陳謝。

お礼

回答有難うございます。
なんか…すごく複雑ですね。
VBAは最近勉強始めた全くの初心者なので、上手く出来ません。。。
教えて頂いた内容を良く読んで、VBAをもっと勉強しながら、がんばってみます！！

回答No.3

noname#67980

1と2と4
MsgBox UCASE(REPLACE(REPLACE("100 ABC-23d=3,4", Space(1),vbNullstring),",","."))

3はちょっと他の方へパス(手間なので)

お礼

回答有難うございます。
教えて頂いた数式をやってみたのですが、上手く出来ませんでした。
質問の文字列は参考例で、様々な数値、パターンがあるんですが
それが問題なのでしょうか？
初心者なので、数式から応用が出来なくて。。。
説明不足で申し訳ございません。
1と2と4だけでも、一度で出来る数式があればかなり助かるのですが。
そのまま使える数式など教えて頂けると嬉しいです。

回答No.2

n-jun

(1)はスペースは１個・２個・３個・・・・
(3)は"-","="以外にはない？"+"とか"/"とか"×"とか"÷"とか。

お礼

回答有難うございます。
＞(1)はスペースは１個・２個・３個・・・・
スペースは有ったり無かったり1個だったり2個だったり。。。

>(3)は"-","="以外にはない？"+"とか"/"とか"×"とか"÷"とか。
あるんです。。。これ以外にも"[" "%" とか色々。。。

これらを一度に処理したいと思うのは、ちょっと手抜き過ぎですね。
客先が正しい品番を入力してくれのが一番なんですけどね。

回答No.1

sakusaker7

1)と3)はまとめられると思いますが、ほかの二つは無理でしょうね。

お礼

回答有難うございます。
まとめるのは難しそうですね。
最近データ作成が多く、楽な方法があれば…と考えてしまいました。
客先が正しい品番を初めから入力してくれると助かるんですけどね。