Excel VBA ActiveSheet.Name でsheet名を分割するには?

質問

name = ActiveSheet.Name
取得したsheet名を指定した文字分だけを区切って変数に代入して表示は可能でしょうか？

例＞
name = "M_P_S1_2900_50_+4" という情報が入っているとします。 これを…

str1 = "M_P_S1"
str2 = "2900"
str3 = "50"
str4 = "+4"

という感じで情報を抜きとりたい。
str1 = Range("A1")
　・
　・
と表示させる。
余談ですが、半角と全角では文字列の抜き取り情報は変わるのでしょうか?　
例＞
東京都_新宿区_百人町_○丁目
↑
は 1bit or 2bit？ それとも1文字として情報は受取るのでしょうか?
アドバイス宜しくお願い致します。

回答No.2

imogasi · ベストアンサー

.が何文字を意味するか不明ですが
Sub test01()
d = Range("A65536").End(xlUp).Row
' MsgBox d
For i = 1 To d
s = Split(Range("A" & i), "_")
str1 = s(0) & "_" & s(1) & "_" & s(2)
str2 = s(3)
str3 = s(4)
str4 = s(5)
' MsgBox str4
Next i
End Sub
Split関数を使い、s(0)から始まること、上限はHbound(s)
です。
For k = 0 To UBound(s)
MsgBox s(k)
Next k
ですべて左から列挙できます。
＞1bit or 2bit？ それとも1文字として情報は受取るのでしょうか
半角は半角で全角は全角で１文字としてくれます、心配要りません。
＞str1 = Range("A1")
　・
と表示させる。
この意味不です。

お礼

四苦八苦しながら色々と試していたら(^^;　やっと結果が得られるようになりました。#1,#2さんありがとうございます。大変参考になりました。ちなみにソースはこんな感じです。

Sub sheet名を分割表示()
sheet_name = ActiveSheet.Name
d = Range("A65536").End(xlUp).Row
For i = 1 To d
strArray = Split(sheet_name, "_")
Next i
str1 = strArray(0) & "_" & strArray(1) & "_" & strArray(2)
str2 = strArray(3)
str3 = strArray(4)
str4 = strArray(5)
Range("A1") = str1
Range("A2") = str2
Range("A3") = str3
Range("A4") = str4
End Sub

補足

アドバイスありがとうございます。Split関数で必要な文字列を抜き出すのは分かりました。
ソース上で何点か不明なところがあるので再度補足させていただきます。

＞d = Range("A65536").End(xlUp).Row
この処理は一体何を意味するのでしょうか？

実際に以下のソースで実行すると、エラーが戻ります。
Sub test01()
name = ActiveSheet.Name
d = Range("A65536").End(xlUp).Row
' MsgBox d
For i = 1 To d
s = Split(Range("A" & i), "_")
str1 = s(0) & "_" & s(1) & "_" & s(2)
str2 = s(3)
str3 = s(4)
str4 = s(5)
' MsgBox str4
Next i
End Sub

＞str1 = s(0) & "_" & s(1) & "_" & s(2)
この部分でエラーになります。

＞s = Split(Range("A" & i), "_")
A1を参照する意味はあるのでしょうか?
sheet名を取得しているはずですが…

これを踏まえて以下に変更
Name = ActiveSheet.Name
s = Split(Name, "_")

＞str1 = s(0) & "_" & s(1) & "_" & s(2)
＞str2 = s(3)
＞str3 = s(4)
＞str4 = s(5)
の部分For文の中にこれを入れる必要があるのでしょうか？

アルゴリズム的に検討違いしていたらごめんなさい(^^;
再度回答お願い致します。

回答No.3

BLUEPIXY

#1です。
そうですね、要求仕様と違いますね。
すみません。
#2の方が書いているように、
配列の範囲の参照は
一般に x が配列のとき
LBOUND(x)で下限が
UBOUND(x)で上限が調べられます。
文字列は＆で連結できます。
なので、
連結したい部分は、
x(0) & "_" & x(1) & "_" & x(2)
でよいです。
＃２の方が書いている通りです。

ある時は、
"M_P_S1_2900_50_+4"
の最初を連結する
ある時は
"東京都_新宿区_百人町_○丁目"
ただ全部を分割すると言う場合には、
データのあり方と処理を考える必要があると思います。
数値以外を連結するとか
数値が出てくるまで連結するとか
あるいは、シート名の規則を変えるとか

回答No.1

BLUEPIXY

'sheetname = "M_P_S1_2900_50_+4"
sheetname = "東京都_新宿区_百人町_○丁目"

x = Split(sheetname, "_")
i = 0
For Each c In x
Range("A1").Offset(i, 0).Value = c
i = i + 1
Next
って感じでできます。
Split関数は、文字列を指定した区切り文字で分割して配列にする関数です。２バイト文字、１バイト文字は関係なく使えます。

補足

表記のコードを実行しましたら、以下のようになりました。
以下のような結果を得たいので再度補足させていただきます。

　　　今回結果　　→　　改善
A1　　　M　　　　　　　M_P_S1　
A2　　　P　　　　　　　2900
A3　　　S1　　　　　　 50
A4　　　2900
A5　　　50
A6　　　4

改善するには、Split()関数で取得したA1～A3の文字列を連結させて表記させる必要があります。
で、変数 x の配列情報を参照方法と、文字の連結方法にはどうすればよろしいのでしょうか?　多分この場合は…

変数(配列) + 変数(配列) + 変数(配列)

になると思いますが…、再度アドバイスよろしくお願い致します。