[[20220808170629]]　『1つの文字列に複数含まれる、同じ記号の特定の間ａx（もこ）　ページの最後に飛ぶ

[削除]『1つの文字列に複数含まれる、同じ記号の特定の間の文字列だけを抽出する関数で想定外の結果になる』（もこ）

○前提

C3セル:「,」で区切られた値が入っている(最大300個)
値は1桁から3桁まで入り、順不同
(例)a,ab,abc,(省略),bb,bc

D3セル:下記の数式でC3セルに出てくる「,」の数を出している
=LEN(C3)-LEN(SUBSTITUTE(C3,",","")

E3セル
D3セルが1もしくは0の場合のC3セルに入ってる値を返す
=if(D3>0,LEFT(C3,FIND","C3)-1),C3)

例
C3にa,b(D3>0)もしくはaが入っていた場合いずれもaが結果に出てくる

F3セル
D3セルが0の時、1の時、2以上の時で結果がかわる

=if(D3=0,"",if(D3=1,RIGHT(C3,LEN(C3)-FIND("●",substitute(C3,",", "●",LEN(C3)-LEN(substitute(C3,",",""))))),IF(D3>=2,TRIM(MID(SUBSTITUTE(C3,",",REPT("'",100)),100*2-99,100))

例
C3にa,b(D3＝1)もしくはa,b,abが二つ(D3>2)入っていた場合いずれもbが結果に出る
C3にa(D3=0)の場合は空白

D3=0〜D3=54までは想定通りの値が出ていますが
D3セル=55以上になった場合に想定外の値が出ます

C3
a,ab,(省略),bb,bc

 ↑   ↑        ↑   ↑
 1     2        54  55(,の左からの個数)
D3セル＝ 55(,が55個存在する)

X3セル
C3セルの左から54番目と55番目の「,」の間の値(bb)を出したい

=if(D3=53,"",if(D3=54,RIGHT(C3,LEN(C3)-FIND("●",substitute(C3,",", "●",LEN(C3)-LEN(substitute(C3,",",""))))),IF(D3>=55,TRIM(MID(SUBSTITUTE(C3,",",REPT("'",100)),100*55-99,100))
→想定した値が出ない(bと出てくる)

○質問

下記の関数を変える必要があると思ってます。
1つの文字列に複数含まれる、同じ記号の何番目と何番目の間の文字列だけを抽出するような関数を調べた時に出てきたものを使って出しており、正直TRIM以降の関数がどういった動きをしているのか理解できてないです。

IF(D3>=55,TRIM(MID(SUBSTITUTE(C3,",",REPT("' ",100)),100*55-99,100))

1. D3＝55以降の値をだすには上の関数をどう変えればいいでしょうか
2. 上記の関数がどんな動きをしているでしょうか

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

 元も子もないかもしれませんが、区切り位置ではダメな理由はあるのでしょうか？
（.:*.ゆ ゅ） 2022/08/08(月) 18:13

.:*.ゆゅさん
コメントありがとうございます。
C3セルの値が別シートの値を参照する関数(=Sheet1!A1)を使っているので区切り位置では厳しいと思ってます…
（もこ） 2022/08/08(月) 18:37

 どのぐらいの頻度で発生する作業なのかにもよりますが
 そのC3セルの値の元をコピーしてきて貼り付けて区切り位置を設定する方が分からない関数を重ねるより楽ではないかなと思ってしまいますが…
 そのあたりは個人の感覚なので仕方ないですかね。

 実際に区切った文字を出したいセルに指定はあるのでしょうか？
 1区切り目、2区切り目…と順番に並んでいくならX列で55番目にはならないですよね？
（.:*.ゆ ゅ） 2022/08/08(月) 18:50

 やりたいことは、E3セルに1つ目の値、F3セルに2つ目の値、･･･と 1つずつ順番に表示させるということ？
 それとも、任意に指定したｎ番目の値を取り出したいということ？

 どっちにしても
 ＞REPT("'"
 なんでシングル・クォーテーションにしてるのか知りませんけど
 数式を修正したところで、最大300個もあるんだったら、その式では難しいんじゃないですかね。

 E3から 1つずつ順番に表示させるのだとして
 ↓ だとどうなりますか？

 E3 =IFERROR(FILTERXML(SUBSTITUTE("<a><b>"&$C3&"</b></a>",",","</b><b>"),"//b["&COLUMN(A3)&"]"),"")
 右コピー
 ※数式を入れるのがどの列からでも、最後の方の COLUMN(A3) は A列のセル番地にすること

 D3セルに　=LEN(C3)-LEN(SUBSTITUTE(C3,",",""))　と入れているのなら
 ↓ の方がいいかも
 E3 =IF($D3+1<COLUMN(A3),"",IFERROR(FILTERXML(SUBSTITUTE("<a><b>"&$C3&"</b></a>",",","</b><b>"),"//b["&COLUMN(A3)&"]"),""))

 C3セルを数式ではなく「値」にできるのなら、区切り位置でやった方がいいでしょう。

 以上
（笑） 2022/08/08(月) 22:24

 IFERROR は要らないか･･･

 E3 =IF($D3+1<COLUMN(A3),"",FILTERXML(SUBSTITUTE("<a><b>"&$C3&"</b></a>",",","</b><b>"),"//b["&COLUMN(A3)&"]"))

 C3が空白ということがなければ、ですけど

 以上
（笑） 2022/08/08(月) 23:27

コメントいただきありがとうごさいます、

一晩経って相当数のセルの管理が必要で、区切り位置自体はエクセルの機能にあることを考えると関数を使うのはやめて
C3セルの値をD3セルに貼り付けるマクロを組んで、D3セルを区切り位置を使って1つづつ出すようにしようと考えております。

たくさんの案を出していただきありがとうございます。

（もこ） 2022/08/09(火) 12:30

 C3セルの値を区切り位置で区切る際に区切り位置のダイアログの3/3で表示先にD3と指定するとC3セルの値はそのままで
 D3セルから区切った値を入れることができる。
（ねむねむ） 2022/08/09(火) 12:39

 ほぼ解決されたようですね。

 ・a,ab,abc,d,e,f,bb,bc のようなデータであれば、下記のtest1でよいでしょう。
 ・a,ab,abc,"d,e",e,f,bb,bcのような文字列の中にカンマを含むようなものがあれば、
   それではNGです。
 ・その場合は、「区切り位置」を使った下記のtest2が適切でしょう。
   (各要素に日付とか特殊な書式のものが入らなければ、fieldinfo引数の指定は省略できます。）

 Sub test1()
     Dim ary As Variant
     Dim s As String

     s = ActiveCell
     ary = Split(s, ",")
     ActiveCell.Offset(, 1).Resize(1, UBound(ary) + 1) = ary
 End Sub

 Sub test2()
     ActiveCell.TextToColumns Destination:=ActiveCell.Offset(, 1), DataType:=xlDelimited, _
         TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
         Semicolon:=False, Comma:=True, Space:=False, Other:=False, TrailingMinusNumbers:=True
 End Sub

 繰り返しを使って、複数の範囲を同時に処理することももちろん可能ですね。（上のコードは限定版です）

 # お二方のコメントを元に、関数使用を別の手段に切り替えたのは賛成です。
 # あえて"沼"に入らず、Excelが提供する道具を積極的に使ったほうが適切だと思いました。
（γ） 2022/08/09(火) 15:35

コメント返信：

[ 一覧(最新更新順) ]

[[20220808170629]] 『1つの文字列に複数含まれる、同じ記号の特定の間ａx（もこ） ページの最後に飛ぶ

[[20220808170629]]　『1つの文字列に複数含まれる、同じ記号の特定の間ａx（もこ）　ページの最後に飛ぶ