[[20230223104800]]　『文字列分割関数(FUNCTION)を作成したい』（諸子頑鉄）　ページの最後に飛ぶ

『文字列分割関数(FUNCTION)を作成したい』（諸子頑鉄）

セル文字列を入力
文字列中の指定文字（文字列）を境に左右で分割して抽出する関数（Function）を作成したいと思います。

指定文字（文字列）が複数存在する場合も対象とします。

例えば、下記の場合、指定文字（文字列）が"_"だとすると６個存在します。

D,E列に結果を書き出す事はワークシート関数及びVBAで一応できましたが
下記のVBAを元にFunctionに改造したいのですがどのように変更したら良いですか　？

できれば、最終（完成形）は、「左から３個めの指定文字（文字列）で左右に分ける」ようにしたいです。
（VBAで言えば、INPUTBOX等でiを直接指定）

又、
素人の考えたコードなのでもっと良き方法があれば紹介下さい。

    |[A]                       |[B]|[C]|[D]                   |[E]                   
 [1]|123_456_789_10_987_654_321|  1|  4|                   123|456_789_10_987_654_321
 [2]|                          |  2|  8|123_456               |789_10_987_654_321    
 [3]|                          |  3| 12|123_456_789           |10_987_654_321        
 [4]|                          |  4| 15|123_456_789_10        |987_654_321           
 [5]|                          |  5| 19|123_456_789_10_987    |654_321               
 [6]|                          |  6| 23|123_456_789_10_987_654|                   321

     |[A]                       |[B]|[C]                 |[D]              |[E]               
 [1]|123_456_789_10_987_654_321|  1|=FIND("_", A1)      |=LEFT($A$1, C1-1)|=MID($A$1,C1+1,30)
 [2]|                          |  2|=FIND("_",$A$1,C1+1)|=LEFT($A$1, C2-1)|=MID($A$1,C2+1,30)
 [3]|                          |  3|=FIND("_",$A$1,C2+1)|=LEFT($A$1, C3-1)|=MID($A$1,C3+1,30)
 [4]|                          |  4|=FIND("_",$A$1,C3+1)|=LEFT($A$1, C4-1)|=MID($A$1,C4+1,30)
 [5]|                          |  5|=FIND("_",$A$1,C4+1)|=LEFT($A$1, C5-1)|=MID($A$1,C5+1,30)
 [6]|                          |  6|=FIND("_",$A$1,C5+1)|=LEFT($A$1, C6-1)|=MID($A$1,C6+1,30)

 Option Explicit

Option Base 1

Sub test()

    Dim T_moji As String
    Dim C_moji As String
    Dim i As Long, cnt As Long

    T_moji = Range("A1")

    C_moji = Application.InputBox(Prompt:="検索する文字を指定して下さい。", Title:="検索文字", Type:=2)

    If InStr(T_moji, C_moji) = 0 Then
        MsgBox "検索する文字が存在しません。"
        Exit Sub
    End If

    For i = 1 To Len(T_moji)
        If Mid(T_moji, i, 1) = C_moji Then
            cnt = cnt + 1
        End If
    Next
    Dim c As Variant
    Dim d As Variant
    Dim e As Variant

    ReDim c(cnt)
    ReDim d(cnt)
    ReDim e(cnt)

    c(1) = InStr(T_moji, C_moji)
    For i = 2 To cnt
        c(i) = InStr(c(i - 1) + 1, T_moji, C_moji)
    Next

    For i = 1 To cnt
        d(i) = Left(T_moji, c(i) - 1)
        e(i) = Mid(T_moji, c(i) + 1, 100)
    Next

    For i = 1 To cnt
        Cells(i, "D") = d(i)
        Cells(i, "E") = e(i)
    Next

End Sub

＜使用 Excel：Excel2021、使用 OS：Windows11 ＞

 FunctionでInputBoxはないんじゃないかなぁ・・・

    Function ufTEXTSPLIT(s As String, dlm As String, split_num As Long, Get_num As Long) As String
        Dim buf As String
        buf = WorksheetFunction.Substitute(s, dlm, Chr(2), split_num)
        On Error Resume Next
        ufTEXTSPLIT = Split(buf, Chr(2))(Get_num - 1)
        On Error GoTo 0
    End Function

    |[A]                       |[B]        |[C]           
 [1]|123_456_789_10_987_654_321|123_456_789|10_987_654_321
 B1=ufTEXTSPLIT($A$1,"_",3,COLUMN(A1))
 右にコピーでこんな結果になるけどどうでしょう？
（稲葉） 2023/02/23(木) 11:58:27

 ちなみに
 =TRIM(MID(SUBSTITUTE($A$1,"_",REPT(" ",1000),3),(COLUMN(A1)-1)*1000+1,1000))
 こんな感じでも結果は同じ
（稲葉） 2023/02/23(木) 12:02:36

＞FunctionでInputBoxはないんじゃないかなぁ・・・

条件提示が誤解有るようなので追加で説明します。

先にINPUTBOXのなので「左からX個めの指定文字（文字列）で左右に分ける　？」
でXに数値を入力するようにして数値をFunctionに引き渡すような事を想定しています。

例えば、
INPUTBOXでX=3を指定すれば「左から３個めの指定文字（文字列）で左右に分ける」の意味で
Function()で（）の中にx=3を指定するような感じです。

サンプルは、A1だけの単一セルがターゲットですが
実務では、A1列などの複数のセルが対象になるのでFOR分で一括処理を想定しています。

提示いただいたFunctionで上記の構想が生かされているの嬉しいです。

ufTEXTSPLITは、前方（左側）を抜き出す関数ですが
後方（右側）抜き出しのFunctionはどのようになりますか　？

（諸子頑鉄） 2023/02/23(木) 12:50:59

 ＞ufTEXTSPLITは、前方（左側）を抜き出す関数ですが
 違います。
 Get_num As Long　←この引数で何番目の文字を取り出すか決めてます。
 さてはB1の式をC1にコピーを試してないな！？

（稲葉） 2023/02/23(木) 13:23:37

＞さてはB1の式をC1にコピーを試してないな！？

はい、ご明察の通りです。
すいません。
最初の質問でシートレイアウトを示したようにB列には数値が既に入力済みなので
とりあえず利用されていないセル（E8)で試してみました。

以下のように表示されたので
E8:　123_456_789

「前方（左側）を抜き出す関数」と判断しました。

B1で試してみるとE8の場合と違って以下のように表示されました。
B1:　12

＞Get_num As Long　←この引数で何番目の文字を取り出すか決めてます。

よく分かつていません。

ufTEXTSPLIT($A$1,"_",3,COLUMN(A1))で言うと
COLUMN(A1)がGet_num As Longに相当すると思うので

COLUMN(A1)は、１でどこのセルに入力してもufTEXTSPLITの結果は変わらはずはないと思うのに
B1とE8では、結果が違います。

なぜに違うのか？
（諸子頑鉄） 2023/02/23(木) 14:45:45

 ＞COLUMN(A1)は、１でどこのセルに入力してもufTEXTSPLITの結果は変わらはずはないと思うのに
 ＞B1とE8では、結果が違います。
 全く同じ数式なら変わるはずないんですが・・・

 ufTEXTSPLIT($A$1,"_",3,1)
 で試してください。

 COLUMN(A1)をあえて使ったのは、数式をコピー（セルごと）した場合に備えて準備したものです。
 VBA内で使う場合、1か2を指定してください。
 それ以外の数値を入れると空白が帰ってきます。

 ufTEXTSPLIT($A$1,"_",3,1)　なら123_456_789
 ufTEXTSPLIT($A$1,"_",3,2)　なら10_987_654_321
 それ以外の数値は空白

 わからなければ、ステップ実行で一個ずつ値確かめてください。

（稲葉） 2023/02/23(木) 15:13:38

＞全く同じ数式なら変わるはずないんですが・・・

失礼しました。

重大なミスをしていました。

B列の列幅を小さく指定していたので
「123_456_789」　の　最初の方の　「12」　のみが見た目で表示されていました。
（表示幅を広げたら結果は、同じ123_456_789でした。）

＞1か2を指定してください。

了解です。

つまり、最後のパラメーターで
前方を取り出すのか？、後方を取り出すのかを？
を指定するのですね。

（諸子頑鉄） 2023/02/23(木) 15:34:32

以下のVBAで使うことを想定して見ましたがどうでしょうか　？

良ければ、「Function 指定文字分割」　をPERSONAL.XLSBに登録予定です。

Sub test2()

    Dim T_moji As String
    Dim C_moji As String
    Dim T_num As Long
    Dim i As Long
    Dim cnt As Single
    Dim houkou As Long

    T_moji = Range("A1")

    C_moji = Application.InputBox(Prompt:="検索する文字列を指定して下さい。", Title:="検索文字列", Type:=2)

    If InStr(T_moji, C_moji) = 0 Then
        MsgBox "検索する文字列が存在しません。"
        Exit Sub
    End If

    For i = 1 To Len(T_moji)
        If Mid(T_moji, i, 1) = C_moji Then
            cnt = cnt + 1
        End If
    Next

R1:

    T_num = Application.InputBox(Prompt:="何番目の文字列で分割しますか", Title:="検索文字", Type:=2)

    If T_num < 1 And T_num > cnt Then
        MsgBox "分割位置の指定が間違っています。　再入力　！！"
        GoTo R1
    End If

R2:

    houkou = Application.InputBox(Prompt:="前方＜左側＞抜き出しですか　？　１" & vbCrLf & _
        "後方＜右側＞抜き出しですか　？　２", Title:="どちら？ 1 or 2", Type:=1)

    If houkou <= 0 And houkou > 2 Then
        MsgBox "方向指定数は、１又は２です。 再入力　！！"
        GoTo R2
    End If

    Range("D8") = 指定文字分割(Range("A8"), C_moji, T_num, houkou)

End Sub

Function 指定文字分割(s As String, dlm As String, split_num As Long, Get_num As Long) As String

    '指定文字分割(ターゲット文字列,分割文字列,何番目,1)　前方（左側）抜き出し
    '指定文字分割(ターゲット文字列,分割文字列,何番目,2)　後方（右側）抜き出し
    Dim buf As String

    buf = WorksheetFunction.Substitute(s, dlm, Chr(2), split_num)

    On Error Resume Next
    指定文字分割 = Split(buf, Chr(2))(Get_num - 1)

    On Error GoTo 0

End Function

（諸子頑鉄） 2023/02/23(木) 16:39:31

 ＞サンプルは、A1だけの単一セルがターゲットですが
 ＞実務では、A1列などの複数のセルが対象になるのでFOR分で一括処理を想定しています。
 これが是なら、test2の作りはどうなんでしょう？
 少なくとも"検索する文字列が存在しません。"とか分割位置の指定が間違っています。　再入力　！！"
 一個ずつ表示させるんですか？
 決まったループ処理ならある程度決まった型式があるはずなので、InputBoxではなく
 シートに直接記入しておくべき内容だと思いますが・・・

（稲葉） 2023/02/23(木) 17:49:37

アドバイスありがとうございます。

処理すべきターゲットがいつも同じ書式ではなく変化するので
シートに定形のfanctionを直接記入すると言う訳には行きません。

＞"検索する文字列が存在しません。
ターゲットの中に検索すべき文字が存在しない異質な行が存在する事も有りえます。

＞分割位置の指定が間違っています。　再入力　！！"一個ずつ表示させるんですか？
見直すと
これは、最初に決めれば良いことなのでオーバースペックで通常必要ないですね。

おっしゃるようにシートに直接記入できそうな場合は、
　「Function 指定文字分割」だけをPERSONAL.XLSBに登録して於けば
　シートにfunctionを直接記入できるので便利かなと考えました。
　
つまり、ターゲットにより使い分けを想定しています。
（諸子頑鉄） 2023/02/24(金) 05:06:11

 おはようございます。 
 ユーザー定義関数についての、ちょっとしたTipsを紹介しておきましょう。

 ヘルプ機能は既定の関数のようにはいきませんが、下記のような方法で少し補うことができます。

 =ufTEXTSPLIT(
 と入力した段階で、
 Ctrl+Shift+Aキーを押すことで、
 =ufTEXTSPLIT(s,dlm,split_num,Get_num)
 が補完されます。
 こうすれば、何が求められているかが分かりやすいでしょう。

 引数の表示は、定義したときのものがそのまま使われるので、
 できるだけ自分にとって分かりやすい引数名にしておくのがよいでしょう。
 　
（γ） 2023/02/24(金) 06:04:33

γさん、ユーザー定義関数の取り扱い方のアドバイスありがとうございます。

 Ctrl+Shift+Aキーを押して以下のように補完されるようになって大変分かりやすくなりました。
指定文字分割(ターゲット文字列 As String, 検索文字列 As String, 何番目 As Long, 抜き出し方向 As Long)

1,2,3のパラメーターまでは直感的に指定できそうですが
４のパラメーター(抜き出し方向)は、1 or 2 の数字のどちらかで指定しますが
　直感的では思い出せなくなる可能性が有ります。
　（文字列で前側、後方の < or > と文字列する事もできますが
　　　これも忘れてしまえば資料を見返す事になります。）
　　　

教えていただいたので
既定の関数のHELP機能のようには行きませんが間違いは少なくなりそうです。
（諸子頑鉄） 2023/02/24(金) 06:52:42

 ＞ シートに直接記入しておくべき内容だと思いますが・・・
 ちょっと意思疎通がうまくいかなかったので、例示しますと
 表の作りを工夫することでシートの数式でもいいかと
 区切り文字がない場合
 　前半は文字列がそのまま
 　後半は空白
 指定の区切り位置がない場合
 　前半は文字列がそのまま
 　後半は空白
 になりますので、エラー処理しなくてもいいのかなーと。

 D2=指定文字分割($A2,$B2,$C2,D$1)　→と↓にコピー
    |[A]           |[B]       |[C]     |[D]           |[E] |[F]                       
 [1]|文字          |区切り文字|指定位置|             1|   2|                          
 [2]|5732_5663_4342|_         |       2|5732_5663     |4342|正常                      
 [3]|5732_5663_4342|_         |      10|5732_5663_4342|    |指定位置がオーバーの場合  
 [4]|1361_417978991|_         |       2|1361_417978991|    |区切り文字が1つの場合     
 [5]|8086-8962-1804|_         |       2|8086-8962-1804|    |区切り文字がない場合      
 [6]|8297_4919-5258|_         |       2|8297_4919-5258|    |区切り文字以外の組み合わせ

 逆にループ処理で行う場合は、引数を渡すときに型がずれているとメイン側でエラーになるので
 型チェックを行うか、OnErrorでFunctionをスキップするかですかね・・・
 ループで行う場合は、↑のように表を作っておいて、以下のような使い方が便利だと思います。
    Sub test()
        Dim tbl As Variant
        Dim r As Long
        tbl = Range("A1:E6").Value
        For r = 2 To UBound(tbl, 1)
            tbl(r, 4) = ""
            tbl(r, 5) = ""
            If IsNumeric(tbl(r, 3)) Then
                tbl(r, 4) = 指定文字分割(tbl(r, 1), tbl(r, 2), tbl(r, 3), 1)
                tbl(r, 5) = 指定文字分割(tbl(r, 1), tbl(r, 2), tbl(r, 3), 2)
            End If
        Next r
        Range("A1:E6").Value = tbl
    End Sub
    Function 指定文字分割(ByVal s As String, ByVal dlm As String, ByVal split_num As Long, ByVal Get_num As Long) As String
        '                 ~~~~~ByVal追加　→　配列から直接引数を渡すときに、ByRefではエラーになる
        '指定文字分割(ターゲット文字列,分割文字列,何番目,1)　前方（左側）抜き出し
        '指定文字分割(ターゲット文字列,分割文字列,何番目,2)　後方（右側）抜き出し
        Dim buf As String
        buf = WorksheetFunction.Substitute(s, dlm, Chr(2), split_num)
        On Error Resume Next
        指定文字分割 = Split(buf, Chr(2))(Get_num - 1)
        On Error GoTo 0
    End Function

（稲葉） 2023/02/24(金) 09:10:09

 よこから失礼します
 2021ならスピルするので、

 Function Split2(ByVal s As String, ByVal delim As String, ByVal pos As Long) As String()
   s = WorksheetFunction.Substitute(s, delim, Chr(2), pos)
   Split2 = Split(s, Chr(2))
 End Function

 でいいかもしれません
 (スピルしないバージョンでは、配列数式として2列選択して Ctrl+Shift+Enter)
（´・ω・`） 2023/02/24(金) 09:31:02

 （´・ω・`）さんありがとうございます。
 こっちのほうがエラーもなくて理に適ってますね！

 状況にもよると思いますが、区切り文字がない、位置がオーバーなどの場合等考えて、
 こんな感じ（後半は空欄）のほうが出力的にピンとくるんですが、どうですかね？
 Split2 = Split(s & Chr(2), Chr(2))
（稲葉） 2023/02/24(金) 11:13:12

´・ω・`　さん、Split2の紹介ありがとうございます。

抜き出し方向を関数に含めなくてもスピル機能で自動で処理されるのでとても便利です。
（今までは第４係数の抜き出し方向を指定しないと処理できなかった）

ちょっと調べたら、アドインに登録したほうが便利そうなので
探していたら以下の動画を見つけました。

OPTIONの設定やユーザー関数にHELP追加できる方法も紹介されているの参考にしました。
（HELPができたら次は、アドインに追加予定です。）

https://www.youtube.com/watch?v=9ABIfi2znq0

HELPを付けようと下記の参考画像のようにして
Sub ヘルプ()を実行しましたがエラーが出ました。

& vbCrLf & _　で繋いで複数行にする事ができない為でしょうか　？

（諸子頑鉄） 2023/02/24(金) 14:43:20

 文字数制限(255文字まで）じゃないですか？
 　
（γ） 2023/02/24(金) 14:59:14

 稲葉さん
  >Split2 = Split(s & Chr(2), Chr(2))
  どちらでも使い易いほうでいいんじゃないでしょうか
  どういう仕様にするかという問題なので。

 諸子頑鉄さん
　MacroOptions の引数に指定するMacro引数は、存在する名前じゃないとそのようなエラーになるようです。

 >Function Split2 
 >Application.MacroOptions "Spli2"
                               ↑ t がない
（´・ω・`） 2023/02/24(金) 15:09:50

 いかにも256文字までは無かったですね。制約自体はありますが。

 余談：
 そういえば、昔は、ヘルプファイルを作成するツールもありましたが、
 ヘルプファイルの形式が変わったこともあり、既に提供されなくなっていると思います。
 数式バーの中で、既定の関数と同じようなインタラクティブな振る舞いをするものはありません。昔も今も。
 　
（γ） 2023/02/24(金) 15:21:16

γさん、稲葉さん　アドバイスありがとうございます。

＞Application.MacroOptions "Spli2"
＞ ↑ t がない

ご指摘のとうり名前をミスっていました。
正規に修正して上手くHELPが表示されるなりました。
（これで忘却してしまうのが防げそうです。）

その後、Split2をアドインに登録して処理できるのを確認。
お陰様でほんとうに便利になりました。

改めて協力ありがとうございました。
お礼申し上げます。

'--------------------------------------
最後にしようと思いましたが

γさんが以下のように書き込まれたのに気が付きました。
＞数式バーの中で、既定の関数と同じようなインタラクティブな振る舞いをするものはありません。昔も今も。

これは、どう言う事でしょうか　？

（諸子頑鉄） 2023/02/24(金) 15:36:52

 リンクがあったりと既定のものとは差異があるんですが、気が付かなければ忘れてください。
 そもそも「できない」という情報には余り価値がなかったですね。少なくともニーズがないのであれば。
 　
（γ） 2023/02/24(金) 17:02:05

＞リンクがあったりと既定のものとは差異があるんですが、気が付かなければ忘れてください。

「気が付かない」が理解できないの意味なら
　　全く理解できていないので忘れる事にします。

＞そもそも「できない」という情報には余り価値がなかったですね。

「インタラクティブな振る舞い」も何のことやらの状況で
更に何が「できない」かも理解できていないので忘れる事にします。

お世話になりました。

（諸子頑鉄） 2023/02/24(金) 17:34:22

コメント返信：

[ 一覧(最新更新順) ]

[[20230223104800]] 『文字列分割関数(FUNCTION)を作成したい』（諸子頑鉄） ページの最後に飛ぶ

[[20230223104800]]　『文字列分割関数(FUNCTION)を作成したい』（諸子頑鉄）　ページの最後に飛ぶ