ExcelでMIDB関数を使用した際の、全角文字

Excelにて、MIDB関数を利用して、文字列切り出しをしようとしています。

その際、「=MIDB(A1,1,20)」の関数にて、A1セルの20バイト付近に漢字が入っている場合、
上手く切り出すことができません。

何か良い方法はありますでしょうか。

No.10 回答者： WindFaller 回答日時： 2017/11/27 00:14

もしも、話がトンチンカンになっていなければ、こんな事で良いのではないでしょうか。

>A1セルの20バイト付近に漢字が入っている場合、
>上手く切り出すことができません。

それは、最初の10バイトとか20バイトの文字を取って
以下のような方法で検索に掛ければよいと思います。

文字一つなら、こういう式で探せます。

abdefg1ef亜1245678

=MID(A1,MATCH(2,INDEX(LENB(ASC((MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)))),,),0),1)


=KanjiSearch(A1)
または、
=KanjiSearch(A1,1) 何個の漢字があれば、その内の何番目か。
4バイト文字でも、異字体でなければ探すことは可能です。

//ユーザー定義関数
'//標準モジュール
Function KanjiSearch(Rng As Range, Optional num As Long = 1)
'Rng=セル, num =何番目か
Dim Txt As String
Dim buf As String
Dim i As Long, j As Long
Txt = Rng.Value
For i = 1 To Len(Txt)
　If Mid(Txt, i, 1) Like "*[一-龠]*" Then
　 buf = buf & Mid(Txt, i, 1)
　 j = j + 1
　End If
Next
If num > j Then num = j
If Len(buf) > 0 Then
KanjiSearch = Mid(buf, num, 1)
Else
KanjiSearch = ""
End If
End Function

No.9 回答者： GooUserラック 回答日時： 2017/11/25 23:35

No.8 の補足

「エクセルで開く」とは下図のようにアドレス指定で開いてください。

No.8 回答者： GooUserラック 回答日時： 2017/11/25 23:16

No.6 の補足

ここでは４バイトの漢字を載せる事が出来ませんので以下をHPを見てください。
http://mojikiban.ipa.go.jp/1792.html
OSとエクセルのバージョンによっても対応できているか微妙ですが異体字はチャント見えるでしょうか？
見えるのならばこのページをエクセルで開いてみてください。
エクセルでもチャント見えましたか？
チャント見えたら「LENB」関数を使ってバイト数を見てください。
緑丸の文字は普通の漢字ですから２バイトです。
赤丸の文字は異体字なので４バイトになります。

なのでバイト数で管理は本当に大変ですよ！

No.7 回答者： WindFaller 回答日時： 2017/11/24 23:05

MIDBとかいうと話がややこしくなりますね。

まず、関数から

=LARGE(INDEX((LEN(MID($A$1,ROW(INDIRECT("A1:A"&LEN($A$1))),1))*2=LENB(MID($A$1,ROW(INDIRECT("A1:A"&LEN($A$1))),1)))*ROW(INDIRECT("A1:A"&LEN($A$1))),,),ROW($A1))

0を排除する方法を考えませんでしたので、Large(大きい順)で出しました。
他の誰かさんが可能にすると思います。

>【VBAのB付き文字列関数は、渡された文字を全てUTF-16文字に変換してから扱うんですよ！！】

それは逆でしょう。（^^;
VBAは、一応、全部Unicdoeになっていますから、それをSJISに戻してから調べないと分からない、ということです。変更になった時のことを、結構、覚えています。<3番目のコード参照>

2番目のコードは、私が昔作ったものですが、もう、私には作れないような気がします。
1番目の方法は、Webでも使われる方法です。

'//文字をbyte配列にして探す1
Sub TowBytechrFind1()
Dim Text As String
Dim strTxt As String
Dim b() As Byte
Dim i As Long, j As Long
Text = "あabcdefghijいklmhnl"
b() = Text
j = 1
For i = 0 To UBound(b()) Step 2
　If b(i + 1) > 0 Then
　 MsgBox Mid(Text, j, 1)
　 DoEvents
　End If
　j = j + 1
Next
End Sub

'//日本字を探す2
Sub TowBytechrFind2()
Dim Text As String
Dim strTxt As String
Dim i As Long
Text = "あabcdefghij漢字klmhnl"
For i = 1 To Len(Text)
　If IsJapanese(Mid(Text, i, 1)) Then
　 MsgBox Mid(Text, i, 1)
　End If
Next
End Sub

Function IsJapanese(arg As String) As Boolean
Dim buf As String
buf = StrConv(arg, vbWide)
buf = StrConv(buf, vbHiragana)
buf = StrConv(buf, vbNarrow)
buf = StrConv(arg, vbFromUnicode)
IsJapanese = Not (Len(arg) = LenB(buf))
End Function

'//SJISに戻す方法3
Sub TowBytechrFind3()
　Dim Text As String
　Dim a As String, i As Long
　Dim aB As Long, c As Long
　Text = "あabcdefghij漢字klmhnl"
　For i = 1 To Len(Text)
　　a = Mid(Text, i, 1)
　　aB = LenB(StrConv(a, vbFromUnicode))
　　c = Len(a)
　　If aB <> c Then
　　　MsgBox a
　　End If
　Next
End Sub

'//--正規表現で漢字を探す
Sub RegExpFindKanji()
Dim Text As String
Dim RegEx As Object
Dim Ms, m
Dim enter_text As String
Set RegEx = CreateObject("VBScript.RegExp")
Text = "あabcdefghij漢字klmhnl"
With RegEx
　 .Global = True: .IgnoreCase = False: .MultiLine = True
　 .Pattern = "([一-龠])" '
　Set Ms = .Execute(Text)
End With
For Each m In Ms
MsgBox m.FirstIndex & "番目　:" & m.Value
Next
End Sub

No.6 回答者： fakeflake 回答日時： 2017/11/24 18:58

ちなみにマクロでMIDBやLEFTBを使えと言っている人は、ひとつ重大なことを忘れていませんか？

【VBAのB付き文字列関数は、渡された文字を全てUTF-16文字に変換してから扱うんですよ！！】

つまり全角だろうと半角だろうと、果ては制御コードすら2バイトにされちゃうという恐るべき仕様です。なんでワークシート関数とVBA関数で極端に挙動が違うんだよクソが！！

…大変失礼しました。つい私情が入っちゃって…

私自身、そんな仕様だとは知らずにマクロを書いてハマった生々しい記憶が…この点はワークシート関数の方がまだ感覚に近いです。なのでマクロで全角半角を区別しようと思ったら、1文字ずつ取り出して文字コードを見て回るしかないんじゃないかと思いますね。こういうのを見てると、webの入力フォームで全角だけで入力してくださいってのは、データ切り出しの手間を省くためなんだろうなとか思って同情しちゃいます。思いっきり余談ですが。

No.5 回答者： GooUserラック 回答日時： 2017/11/24 18:38

さらに Unicode を使っていると漢字自体が２バイト文字とは限りませんのでさらに複雑になりそうですよ！

ちなみに「MIDB関数を利用」とのことですが、ワークシートの関数でしょうか？それともマクロ（ＶＢＡ）の関数でしょうか？マクロ（ＶＢＡ）の方ならいくらでも逃げ道がありそうです。

No.4 回答者： fakeflake 回答日時： 2017/11/24 18:33

> まさに２０バイト目に漢字があった場合には、項目２に設定しようとしております

そこをもう一歩突っ込んで考える必要があります。具体的にどうすればいいのかを。ちょっと考えただけでも、以下の懸念があります。

a)項目1は19バイトでいいのか？それともぶった切った結果のスペースのままで20バイトにするか？

b)ぶった切り20バイトの場合、項目1で元々20バイト目に普通の半角スペースが来ることはないのか？これがイエスだと区別をどうする？

c)項目2に20バイト目の漢字を繰り入れると、項目2が2バイト長くなってしまうが問題ないのか？

d)項目2と3の境界線でも、全角文字をぶった切ってしまう問題が起きるのではないか？

No.3 回答者： don_go 回答日時： 2017/11/24 18:29

Left()関数で20文字取得して、LeftB()でバイト数を求めて20バイト

以上の場合は1文字ずつ減らして20バイト以下になるまで繰り返す。
そういうVBAモジュールを作成してみては？

No.2 回答者： fakeflake 回答日時： 2017/11/24 18:09

ExcelのMIDB関数は、全角文字（2バイト文字）を途中でぶった切ると、スペースに変換される仕様みたいです。

まあでもそうしないと、おかしなコードの文字が返ってきてしまう危険性があるので、これはやむを得ないでしょう。

今回のような、まさに20バイト目に全角文字が入っていた場合にMIDBで切り出すと、そこに何の漢字が入っていようとスペースで戻ってきますが、それではまずいという話ならば、どうなったらいいんですか？もし漢字を返せと言うのは、それは問題です。なぜならそうすると戻りは21バイトになっちゃいますからね。

もしかするとデータ構造自体の見直しに発展しかねない、厄介な問題だと思います。

この回答へのお礼

ご返答ありがとうございます。

商品マスタの商品名をメンテナンスするに伴い、20バイトを項目１に設定。２０バイトを超えるものについて、項目２および３・・・に設定しようと
しています。
まさに２０バイト目に漢字があった場合には、項目２に設定しようとしております。

お礼日時：2017/11/24 18:15

No.1 回答者： yokomaya 回答日時： 2017/11/24 17:48

MIDでなくMIDBを使う理由が分からないと

適切にお答え出来ないと思います。
全角文字かのチェックをするのはVBAに
なりそうな気もしますし。

この回答へのお礼

ご返答ありがとうございます。

A1セルの文字を、20バイトに収まるように切り出したいと考えており、
MIDBを使用しようとしております。

お礼日時：2017/11/24 17:57