Excel : カンマ区切りの文字列からn番目の項目の文字列を取り出す

2 11月, 2013 (10:14) | パソコン | By: ohishi

カンマ区切りの文字列から、10番目の項目の文字列を取り出したい!

..なんて時、Find関数を使って取り出すことは可能だが、かなりめんどくさい。

どうしようかと考えているうちに、見つけちゃいました。 感謝です。

◆参照元: http://oshiete.goo.ne.jp/qa/4826160.html

ユーザー定義関数って使ったことがなかったですが、VBAのsplit関数をセル上の関数として使用できるんですね
ちなみに、ユーザー定義関数は標準モジュールを挿入して、下記のソースを張り付ければOK

Excelオプション > リボンのユーザー設定 > 開発のチェックをオンにする
開発タブ > Visual Basic > 挿入 > 標準モジュール

 

◆カンマ区切りからn番目の項目を取り出す

Function u_split(str1 As String, str2 As String, str3 As Long)
    Dim x
    x = Split(str1, str2)
    u_split = ""
    If (str3 > 0) And (str3 <= UBound(x) + 1) Then
        u_split = x(str3 - 1)
    End If
End Function

◆使用例

例えばA1に aaa,bbb,ccc,ddd が入力されていて ccc を表示するなら
=u_split(A1,",",3)

A1に aaa-bbb-ccc-ddd が入力されていて ddd を表示するな
=u_split(A1,"-",4)

 

◆カンマ区切り文字列から、先頭からn番目の項目まで取り出す

Function u_split2(str1 As String, str2 As String, str3 As Long)
    Dim x
    If Right(str1, 1) = str2 Then
        str1 = Left(str1, Len(str1) - 1)
    End If
    x = Split(str1, str2)
    u_split2 = ""
    If str3 > 0 Then
        For i = 0 To str3 - 1
            If i <= UBound(x) Then
                u_split2 = u_split2 & x(i) & str2
            End If
        Next
        u_split2 = Left(u_split2, Len(u_split2) - 1)
    End If
End Function

◆使用例
例えばA1に a,bb,c,ddd が入力されていて a,bb,c を表示するなら
=u_split2(A1,",",3)

A1に aaa-bbb-ccc-ddd が入力されていて aaa-bbb を表示するなら
=u_split2(A1,"-",2)

 

◆おまけ1:指定セルから上部10セル内で最初の文字列を返す

一つ上のセルの値を参照する必要があるのだけれど、空行が挿入されてしまった為、
2行上を参照する必要があるみたいな場合に便利

Function u_up10(str1 As Range)
    Dim x
    For i = 0 To 9
        x = str1.Offset(i * -1, 0).Value
        If (x <> "") Or (str1.Offset(i * -1, 0).Row = 1) Then
            Exit For
        End If
    Next
    u_up10 = x
End Function

使い方:こんな感じ
kaisou

 

◆おまけ2: 階層レベルをもとに階層番号を返す

左端の階層を指定すると、階層番号を自動的に作成してくれる

階層	階層番号
1	1
2	1.1
3	1.1.1
4	1.1.1.1
3	1.1.2
2	1.2
3	1.2.1
4	1.2.1.1
5	1.2.1.1.1
4	1.2.1.2
4	1.2.1.3
1	2
2	2.1
3	2.1.1
2	2.2
3	2.2.1
4	2.2.1.1
5	2.2.1.1.1
Function u_kaisou(level As Range, kaisou As Range)
    If level.Value <> 1 Then
        If u_split(u_up10(kaisou), ".", level.Value) = "" Then
            u_kaisou = u_split2(u_up10(kaisou), ".", level.Value - 1) & ".1"
        Else
            u_kaisou = u_split2(u_up10(kaisou), ".", level.Value - 1) & "." & Val(u_split(u_up10(kaisou), ".", level.Value)) + 1
        End If
    Else
        If u_split(u_up10(kaisou), ".", level.Value) = "" Then
            u_kaisou = "1"
        Else
            u_kaisou = "" & Val(u_split(u_up10(kaisou), ".", level.Value)) + 1

        End If
    End If
End Function

使い方:こんな感じ
無題

Write a comment