エクセルのマクロにＬＥＦＴ関数のような働きはありませんか？

質問

顧客データから抽出したデータの、書式を整える作業を自動記録したマクロを使ってやっています。
データの住所欄から、市か郡から下の部分を削除する作業があるのですが、抽出される顧客データは毎日違うので、自動記録のマクロでは出来ないため、この部分は手作業で行なっています。
この「住所の市または郡から下の部分を削除する作業」をマクロで行なう方法は無いでしょうか？

自動記録のマクロでは以下のようになっています。
Cells.Replace What:="○○県××市○○町１－２－３", Replacement:="○○県××市", LookAt:=xlPart, _SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ReplaceFormat:=FalseCells.Replace What:="○○県＊＊市△△町４－５－６", Replacement:="○○県＊＊市", LookAt:=xlPart, _SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

マクロに、ワークシート関数の=LEFT（A1,（FIND（"市",A1）））と同じ様な働きを記述できればいいのか？とも思うのですが、やり方がわかりません。

回答No.6

Wendy02 · ベストアンサー

こんにちは。

データベースで調べてみましたが、都道府県のあとの処理は、なかなか完全にはできません。Replace では、思い切って上書きでは、できません。データの横に出して正しくできているか調べるほうがよいです。今の段階では、完ぺきとはいえませんから。

例として、市が二つ続くのは、全国で3つあります。
「栃木県今市市」、「三重県四日市市」、「広島県廿日市」です。

「千葉県八日市場市安久山」は、日市の後の字ではありません。八日市場市が正解です。

また、「福島県郡山市」　や　「愛知県蒲郡市旭町」　のように、郡のあとに市かありますから、郡山市　蒲郡市が、正解です。「兵庫県神崎郡市川町浅野」や「富山県中新川郡上市町旭町」は、郡のあとに、市が来ますが、神崎郡　新川郡　までです。同じく、「北海道余市郡余市町」の場合は、市のあとに、郡があって、その後に、市がまた来ます。
余市郡が正解です。

「鹿児島県日置郡市来町大里」では、郡のあとに、市がきますが、日置郡が正解です。

エラーチェックは、読み仮名できるような気がしますが、今のところは必要なさそうです。都道府県を区切るならともかく、「市・郡・区」を、関数式で区切るのは少し無理のような気がします。

今、すべてのものを以下のコードで、正しく区切れているか分かりません。

出力場所と、データ範囲は、任意です。適当に変えてください。

'標準モジュール
'---------------------------------------------

Sub SplitOverLetters()
　　Dim i As Integer, j As Integer, k As Integer
　　Dim m As Integer, n As Integer
　　Dim c As Variant
　　Dim buf As String, buf2 As String, buf3 As String
　　Dim ret As String
　　Const COL As Integer = 1 '出力先（データに対して何列目・右)

　　Application.ScreenUpdating = False
　　For Each c In Range("A1:A20") 'データ範囲
　　　　If c.Value <> "" Then
　　　　　　If c.Value Like "*[都道府県]*" Then
　　　　　　　　If Mid(c.Value, 4, 1) = "県" Then
　　　　　　　　　　i = 1
　　　　　　　　Else
　　　　　　　　　　i = 0
　　　　　　　　End If
　　　　　　　　buf = Left(c.Value, 3 + i)
　　　　　　　　buf2 = Replace(c.Value, Left(c.Value, 3 + i), "")
　　　　　　　　i = InStr(buf2, "郡")
　　　　　　　　j = InStr(buf2, "市市")
　　　　　　　　k = InStr(buf2, "場市")
　　　　　　　　m = InStr(buf2, "市")
　　　　　　　　n = InStr(buf2, "区")
　　　　　　　　If i > 2 Then
　　　　　　　　　　buf3 = Mid(buf2, 1, i)
　　　　　　　　　　ret = buf & buf3
　　　　　　　　End If
　　　　　　　　If j > 1 And ret = "" Then
　　　　　　　　　　buf3 = Mid(buf2, 1, j + 1)
　　　　　　　　　　ret = buf & buf3
　　　　　　　　End If
　　　　　　　　If k > 2 And ret = "" Then
　　　　　　　　　　buf3 = Mid(buf2, 1, k + 1)
　　　　　　　　　　ret = buf & buf3
　　　　　　　　End If
　　　　　　　　If m > 1 And ret = "" Then
　　　　　　　　　　buf3 = Mid(buf2, 1, m)
　　　　　　　　　　ret = buf & buf3
　　　　　　　　End If
　　　　　　　　If n > 1 And ret = "" Then
　　　　　　　　　　buf3 = Mid(buf2, 1, n)
　　　　　　　　　　ret = buf & buf3
　　　　　　　　End If
　　　　　　　　c.Offset(, COL).Value = ret
　　　　　　End If
　　　　End If
　　　　ret = ""
　　Next c
　　Application.ScreenUpdating = True
End Sub

お礼

細かく調べていただいて、ありがとうございます！
皆様の回答を読ませていただいて（最後の"市"で区切ったほうが良いとか、市と郡だけでなく区もあるとか）マクロが自分で組める事より（もちろんそれも素晴らしいですが）私にはひらめきが足りないなあと思いました。
同じ結果を求めるにも、アプローチの仕方でずいぶん違うんでしょうね。
Wendy02さんの書いてくださったＶＢＡ、さっぱり理解できない自分が残念です。勉強したいと思います。ありがとうございました！

補足

質問を締め切ります。
現時点では、内容がサッパリ分かってないのが自分でとっても残念ですが、とても細かく調べていただいたお礼にポイントを。

回答No.5

xls88

大雑把ですが参考出品です。

Sub test1()
Dim c As Range
Dim adr As String
Dim n As Integer

For Each c In Range("A1:A50")
adr = c.Value
If adr Like "*県市*" Then
n = InStr(Replace(adr, "県市", "KS", 1, 2), "市")
ElseIf adr Like "*県郡*" Then
n = InStr(Replace(adr, "県郡", "KG", 1, 2), "市")
ElseIf adr Like "*市市*" Then
n = InStr(adr, "市市") + 1
ElseIf adr Like "*市*" Then
n = InStr(adr, "市")
ElseIf adr Like "*郡*" Then
n = InStr(adr, "郡")
ElseIf adr Like "*区*" Then
n = InStr(adr, "区")
End If
MsgBox Left(adr, n)
Next c
End Sub

お礼

私自身マクロがまったく判らないまま質問したのですが、皆さん親切な回答を下さって、恐縮しています。
ＶＢＡの解説本やサイトで、書いていただいた意味を調べ調べして勉強します。ありがとうございました！

回答No.4

374649

例外は多々あると思いますが、単純にこんなものを作ってみました。

Sub 住所()

Dim 住所 As String
Dim No As Integer
Dim No2 As Integer
Dim Adrs As String
Dim i As Integer
Dim Shi As Variant

Shi = "市"

住所 = Range("A1")
No = Application.WorksheetFunction.Find("市", 住所, 1)
No2 = No + 1

Adrs = Left(住所, No2)

If Shi = Right(Adrs, 1) Then
　Adrs = Left(住所, No2)
Else
　Adrs = Left(住所, No)
End If

Range("A2") = Adrs
End Sub

”市”を見つけて左側の住所を取り出します、そのとき”五日市市”のように次に”市”が来たときをチェックしてます。

例外はありますがすべて手作業より例外だけ手作業でやればいくらか手間が省けると思いますが。

参考までに！！

お礼

そもそもの住所データがしっかり設計されていないので、難しいとは思っていたのです。
でも毎日この作業にとられる時間がもったいなくて。
いくらかでも手間が省けたら、毎日の作業なので大変助かります。
ＶＢＡを知らないので、なかなか理解できそうにないため、勉強して試してみます。ありがとうございました！

回答No.3

suo2k

> =LEFT（A1,（FIND（"市",A1）））
これと同じなら
Left(Cells(1,1).value,Instr(1,Cells(1,1).value,"市"))
かな。

ですが、住所の操作って意外と難しいですよ。
例えば、○○県五日市市北市町市市荘999-999
みたいな住所にその関数（最初の"市"のとこで切る）だと、
「○○県五日市」で切れちゃいますよね？
市名と町名の間にスペースでも無い限りは完全な判別は不可能かと思います。

なので、最初ではなく最後の"市"（郡）で切り取って、目で確認し、
おかしいところを手で削除、というのがいいかな…
以下一例
「処理行」「最終行」「文字位置」「文字内容」は変数使ってください。
---------------------------
（変数宣言）

For 処理行 = 1 to 最終行
文字内容 = Cells(処理行, 1).Value
For 文字位置 = Len(文字内容) To 1 Step -1
If Mid(文字内容, 文字位置, 1) = "市" Or Mid(文字内容, 文字位置, 1) = "郡" Then
Cells(処理行, 1).Value = Left(文字内容, 文字位置)
Exit For
End If
Next 文字位置
Next 処理行
----------------------------
例ではA列を上から順に移動し、セル内の右側から一つずつ文字を調べて、
市か郡が見つかったらそこで文字を切り取ります。
あとはオートフィルタとか、一個ずつカーソル移動しての確認が必要ですね。
もっと良い方法ありそう＾＾；

お礼

マクロのＶＢＡと言うの物がわからないもので・・・。
最初はエクセルに関数を記述しようと思っていたんです。
住所データとは別の列に、市までを入力させて、例外は手作業になっても、かなり手間が減らせるなと。
それなら=LEFT（A1,（FIND（"市",A1）））でいいのかな？と言うのが考え始めでした。
五日市市・・・なるほど、確かに最後の"市"で切り取るほうが良いですね。
考え方の参考になります。ありがとうございました。

補足

皆さんからの回答をみて、マクロにやらせたかった事が思ってたより難しい事がよく分かりました。
完璧でなくても、一部でも手間が省けたらいいなと思った時に、自分が一番早く理解できそうな回答はこちらだと思いました。

回答No.2

n-jun

Excel：住所を区切る
http://www.geocities.jp/chiquilin_site/data/050228_delimination_of_address.html

「郡」や「市」という判定はちょっとややこしそうです。
該当する物がデータ内になければ、大丈夫かもしれません。

お礼

そもそもの住所データがしっかり必要に応じて区切られて出来ていれば良かったんですが・・・。
市や郡で区切るといってもなかなか難しいようですね。
参考になります。ありがとうございました。

回答No.1

INTLINSIDE

MidとInStr等でできそうです。

http://www.geocities.jp/cbc_vbnet/function/mojireyu_function.html

お礼

勉強になるサイトですね。
日々使うマクロがもう少し便利だといいのにと思って質問したのですが、ＶＢＡと言うものは全く知らないのものですから・・・。
これから少しずつ勉強します。ありがとうございました。