VBA文字列を分割して配列を作成 Split

エクセルバージョン

マイクロソフト365

今回は文字列を区切って配列にできるSplit関数についてです。

それではいってみましょう！

1 Split関数
- 1.1 構文
- 1.2 引数
2 サンプル
3 応用
4 まとめ

Split関数

概要

文字列を分割して、1次元配列を返します。

構文

Split(対象文字[,区切り文字] [,配列数] [,区別] )

引数

Split	必須/省略可	詳細
対象文字 (第1引数)	必須	対象の文字やセルを指定。
区切り文字 (第2引数)	省略可	分割するための文字や記号を指定。・省略 → 半角空白文字(” “)
配列数 (第3引数)	省略可	配列数を指定。・省略(全部) → -1。
区別 (第4引数)	省略可	区別の種類を指定(英字の大文字と小文字を区別)。・省略 → 区別する。・vbBinaryCompare → 区別する。・vbTextCompare → 区別しない。

サンプル

全て標準モジュールにコピペで即試せます(*ﾟ▽ﾟ)ﾉ

Option Explicit

Sub split_sample()

Dim head, spAry() As String

head = "第2引数の区切り記号の省略は半角空白"

spAry = Split("1 2 3")

Call res(spAry, head)

'【結果】

'spAry(0) → 1

'spAry(1) → 2

'spAry(2) → 3

head = "第2引数の区切り記号がカンマの場合"

spAry = Split("1,2,3", ",")

Call res(spAry, head)

'【結果】

'spAry(0) → 1

'spAry(1) → 2

'spAry(2) → 3

head = "第2引数の区切り記号が半角空白の場合"

spAry = Split("牛丼豚丼カツ丼", " ")

Call res(spAry, head)

'【結果】

'spAry(0) → 牛丼

'spAry(1) → 豚丼

'spAry(2) → カツ丼

head = "第3引数のlimitを1にした場合"

spAry = Split("牛丼,豚丼,カツ丼", ",", 1)

Call res(spAry, head)

''【結果】

'spAry(0) → 牛丼豚丼カツ丼

head = "第3引数のlimitを2にした場合"

spAry = Split("牛丼,豚丼,カツ丼", ",", 2)

Call res(spAry, head)

''【結果】

'spAry(0) → 牛丼

'spAry(1) → 豚丼カツ丼

head = "第3引数のlimitを3にした場合"

spAry = Split("牛丼,豚丼,カツ丼", ",", 3)

Call res(spAry, head)

''【結果】

'spAry(0) → 牛丼

'spAry(1) → 豚丼

'spAry(2) → カツ丼

head = "第4引数のcompareをvbBinaryCompare指定の場合"

spAry = Split("牛丼a豚丼aカツ丼", "A", , vbBinaryCompare)

Call res(spAry, head)

''【結果】

'英字の大文字小文字を区別するので分割されません。

'spAry(0) → 牛丼a豚丼aカツ丼

head = "第4引数のcompareをvbTextCompare指定の場合"

spAry = Split("牛丼a豚丼aカツ丼", "A", , vbTextCompare)

Call res(spAry, head)

'【結果】

'英字の大文字小文字を区別しないので分割します。

'spAry(0) → 牛丼

'spAry(1) → 豚丼

'spAry(2) → カツ丼

End Sub

Sub res(a, b)

Dim i As Integer

Debug.Print b

For i = 0 To UBound(a)

Debug.Print "spAry" & "(" & i & ") → " & a(i)

Debug.Print vbCrLf

End Sub

Debug.Print

応用

電話番号を連結

電話番号のハイフン（-）を分割記号にした例。

Sub split_tel1()

'電話番号のハイフンを連結

Dim tel, sp() As String

'ハイフンで区切った値を配列に格納

sp = Split("090-1234-7890", "-")

'番号を連結

tel = sp(0) & sp(1) & sp(2)

Debug.Print tel

End Sub

【結果】

3つの配列に分割して格納後、数字を連結して取得。

複数の電話番号を連結

Sub split_tel2()

'複数の電話番号のハイフンを連続で連結する

'定数Const

Const STARTROW As Integer = 1 '行頭番号を指定

Const DATACOL As String = "A"　 'データの列を指定

Const WRITECOL As String = "B"　'書きだす列を指定

'変数

Dim spAry() As String

Dim i, lastRow, aryNum As Integer

'データの最終行番号を取得

lastRow = Cells(Rows.Count, DATACOL).End(xlUp).Row

'繰り返し処理(For～Next)

For i = STARTROW To lastRow

'ハイフンで区切った値を配列に格納

spAry = Split(Cells(i, DATACOL), "-")

'先頭の0を表示する書式を設定

Cells(i, WRITECOL).NumberFormatLocal = "@"

'エラー無視

On Error Resume Next

'電話番号を連結する

For aryNum = 0 To UBound(spAry)

spAry(0) = spAry(0) + spAry(aryNum + 1)

'セルに書きだす

Cells(i, WRITECOL) = spAry(0)

End Sub

【結果】

B列に複数の電話番号を連続で連結して出来上がりです(*^^)v

メールの＠マークの後ろのドメインを取得

メールの＠マークを分割記号にした例。

Sub split_mail1()

'メールの＠の後ろ(ドメイン)のみ取得

'変数

Dim sp

'＠マークで区切って配列に格納

sp = Split("1528654@yahoo.co.jp", "@")

'2番目の配列を表示させる

Debug.Print sp(1)

End Sub

【結果】

yahoo.co.jp

メールの＠マークの後ろのドメインのみ取得できます。

複数のメールの＠マークの後ろのドメインを連続取得

Sub split_mail2()

'メールの＠の後ろ(ドメイン)のみを連続取得

'変数

Dim spAry() As String

Dim i, lastRow As Integer

'データの最終行番号を取得

lastRow = Cells(Rows.Count, 1).End(xlUp).Row

'繰り返し処理(For～Next)

For i = 1 To lastRow

'@マークで区切った値を配列に格納

spAry = Split(Cells(i, 1), "@")

'エラー無視でセルに書込み

On Error Resume Next

Cells(i, 2) = spAry(1)

End Sub

【結果】

B列に複数のメールの＠マークの後ろのドメインのみ連続して取得できます(^o^)o

MidとInstrを使用したメールドメイン抜き出しは下記

メール@の後ろ（ドメイン）を抜出し取得する

CSVファイルをセルに書き出す

カンマ(，)を分割記号にしてセルに書き出す例。

Sub csv_Sample()

'CSVファイルを1行ずつ読込、セルに1行ずつ書き込む

Dim fNum, rowNum, colNum As Integer

Dim data, spAry() As String

'使用可能なファイル番号の取得

fNum = FreeFile

'開くファイルを読込モードに設定してフルパスとファイル番号を設定

Open "D:\sample.csv" For Input As #fNum

'行番号

rowNum = 1

'ファイルの最終行まで処理（EOFがFalseの間）

While EOF(fNum) = False

'1行読込

Line Input #fNum, data

'読み込んだデータをカンマで区切って配列に格納

spAry = Split(data, ",")

'列番号

For colNum = 0 To UBound(spAry)

'格納された配列データをセルに1行分書き込む

Cells(rowNum, colNum + 1) = spAry(colNum)

'次の行に移行でプラス1する

rowNum = rowNum + 1

Wend

'ファイルを閉じる

Close #fNum

End Sub

【結果】

CSVファイルのデータを1セルずつ書出していきます＼(^^)／

その他のファイルの書出しは下記。

ファイルを読み込みセルに書き出す

まとめ

頭を捻って考えれば、まだまだできることは沢山あるとは思いますが、今回は電話番号、メール、CSVを例に応用してみました(^o^)o

以上です。

Split関数

構文

引数

サンプル

応用

電話番号を連結

複数の電話番号を連結

メールの＠マークの後ろのドメインを取得

複数のメールの＠マークの後ろのドメインを連続取得

CSVファイルをセルに書き出す

まとめ

配列関数

VBA配列（基礎）

VBA配列（結合・分割）

VBA配列（追加,削除,コピー,置換）

関連記事

Excel