Excel関数 セル内の不特定数の特定文字の次の1文字の連続抽出

Question

実現したいこと

Excel関数を使ってセル内に不特定数ある特定文字を検索して、各々の次の1文字を連続して全て抽出するにはどうすればいいでしょうか。

発生している問題・分からないこと

2023/09/25に”otn”さんに以前回答していただいたのですが、ひとつのセル内に新セル関数を使わずに数式を入力して処理する必要が出てきたので、再度改めて質問させていただきました。
数式は複雑で長くなってもいいので新セル関数を使わずにひとつのセル内のひとつの数式を入力して処理できればいいと思っています。
ご教授のほどよろしくお願いします。

該当のソースコード

特になし

試したこと・調べたこと

• teratailやGoogle等で検索した
• ソースコードを自分なりに変更した
• 知人に聞いた
• その他

上記の詳細・結果

わかりませんでした。

補足

特になし

Comments

[yambejp]

質問内容がまったく伝わってきません
すでに回答があるならそのリンクをはって、今回はどこが違うかなどを提示ください

[addresshopeph]

https://teratail.com/questions/2keo99qgaae9bq?_from_e=e_bas_showd
新関数が使えない環境下でひとつのセルに数式を入力して処理するにはどうしたらいいでしょうか。

[yambejp]

前回のotnさんの例示の「abcaxyapqからaを指定してbxpを得る」で良いのでしょうか？
この場合、aが連続していたり最後の文字だったりした場合どうするのでしょうか？
また指定する文字は2文字以上だったりする場合は想定しなくてもよいでしょうか？

[addresshopeph]

はい。そのとおりです。

[addresshopeph]

指定する文字は1文字です。
aが連続していたり最後の文字だったりすることはありません。

[yambejp]

もう一つ確認、VBAでユーザー関数を作ったりしてはだめですか？
あくまで古いExcelの関数で完結していないといけない？

[addresshopeph]

できればVBAでユーザー関数で完結したいです。
VBAでのユーザー関数の場合、希望のの環境下で動作するでしょうか。
Excelにあまり詳しくなくて申し訳ありません。

[addresshopeph]

訂正。
できれば、古いExcelの関数で完結したいです。
VBAでのユーザー関数の場合、希望の環境下で動作するでしょうか。
Excelにあまり詳しくなくて申し訳ありません。

[otn]

先の質問で、
> 新機能が使えないExcel（上記関数が#NAME?エラーになるバージョン）なら、繰り返し処理・配列処理できないので、補助セルを使うしかないと思います。

と書いて補助セルを使った方法まで書いたのですが、読んでないのでしょうか？
その上で、補助セルを使わないで出来ないかという質問なら、VBAを使うしか無いですね。
> VBAでのユーザー関数の場合、希望の環境下で動作するでしょうか。
VBAもこの程度の処理なら、古いExcelでも問題なく書けます。

あるいは、Excelのバージョンアップは難しいとしても、「補助セルを使えない」という条件を解消するか。これは補助セルを別シートにするなどすれば簡単に解消できます。

[addresshopeph]

新エクセル関数で動作させていただいていたのですが、この度新機能が使えない環境下でひとつのセルに数式を入力する必要が出てきました。
申し訳ございません。

[otn]

それは、最初から質問文に書いてあるので、それと全く同じ事をコメントに書くのは無意味です。

もし、私のコメントの意味が分からないので、質問文と同じ事を繰り返し書いたということなら、「コメントの意味が分からない」と書くべきです。

[addresshopeph]

わかりました。
申し訳ございませんでした。

Answer 1

いったんVBAの処理マクロなどVB画面を開き

Function nextstrings(orgStr As String, searchStr As String) As String
  Dim ret As String
  Dim i As Integer
  ret = ""
  For i = 1 To Len(orgStr) - 1
    If Mid(orgStr, i, 1) = searchStr Then
      ret = ret & Mid(orgStr, i + 1, 1)
    End If
  Next
  nextstrings = ret
End Function

のようにユーザー関数をつくっておき

=nextstrings("abcaxyapq", "a")

などでいけます。
VBAで処理したくない場合繰り返し処理は難しいのでひたすらifを使った冗長な処理になります
その場合"a"の出現回数に制限などないとひたすら長い処理が必要になるのであまり現実的ではありません

defghabcaxyapqからaを指定してdbxを得る

Function nextstrings(orgStr As String, searchStr As String) As String
  Dim ret As String
  Dim i As Integer
  ret = ""
  For i = 1 To Len(orgStr) - 1
    If Mid(orgStr, i, 1) = searchStr Then
      ret = ret & Mid(orgStr, i + 1, 1)
    End If
  Next
  ret = Left(ret, Len(ret) - 1)
  nextstrings = ret
End Function

defghabcaxyarsmnからaを指定してdbxr smn

Function nextstrings2(orgStr As String, searchStr As String) As String
  Dim ret As String
  Dim i As Integer
  Dim lastPos As Integer
  ret = Left(orgStr, 1)
  For i = 1 To Len(orgStr) - 1
    If Mid(orgStr, i, 1) = searchStr Then
      lastPos = i
      ret = ret & Mid(orgStr, i + 1, 1)
    End If
  Next
  ret = ret & " " & Right(orgStr, Len(orgStr) - lastPos - 1)
  nextstrings2 = ret
End Function

Comments

[addresshopeph]

わかりました。助かります。
恥ずかしながら、ユーザーを扱ったことがないので詳しい案内を含めてよろしくお願いします。

[addresshopeph]

どうもありがとうございます。
WordはVBAを少しやったことがあるのですが、Excelに関してはVBAは全くの未経験です。
設定の仕方等を詳しくご教授くだされば助かります。
よろしくお願いします。

[addresshopeph]

Excel VBAのModule1(コード) (General)
のウィンドウに書いてくださったユーザー関数を入力するところまでいきました。
このあとはどう進んでいけばいいのでしょうか。

[addresshopeph]

ご指定してくださったユーザー関数が動作しました。
ただ、結果の値の最初に元になる値の最初の文字を加えたいのですが、コードをどうしたらいいでしょうか。

[yambejp]

「結果の値の最初に元になる値の最初の文字を加えたい」の意味がわかりません。
具体的にどうしたらどうなるのをご希望でしょうか？

=nextstrings("abcaxyapq", "a")
が「bxp」ではなく「abxp」になればいいということでしょうか？

[addresshopeph]

先頭文字がaではない。
元になる値の先頭文字を抽出された文字の先頭に加える。
「defghabcaxyapqからaを指定してdbxpを得る」
ということです。

[yambejp]

orgStrの先頭文字を抽出したいならこう
Function nextstrings(orgStr As String, searchStr As String) As String
Dim ret As String
Dim i As Integer
ret = Left(orgStr, 1)
For i = 1 To Len(orgStr) - 1
If Mid(orgStr, i, 1) = searchStr Then
ret = ret & Mid(orgStr, i + 1, 1)
End If
Next
nextstrings = ret
End Function

[addresshopeph]

うまく動作して希望がかないました。
いろいろとお手数をおかけして申し訳ありません。
ご丁寧なご回答心より感謝します。
どうもありがとうございました。

[addresshopeph]

うまく動作して希望がかないました。
いろいろとお手数をおかけして申し訳ありません。
ご丁寧なご回答心より感謝します。
どうもありがとうございました。

[addresshopeph]

追加してお伺いしたいことが出てきました。
最後のaの次の文字を抽出しない
つまり、最後に出てくるaの次の文字を抽出した最後の文字に加えない場合、
コードはどうなるでしょうか。
「defghabcaxyapqからaを指定してdbxを得る」
ということです。

[addresshopeph]

訂正します。
追加してお伺いしたいことがあります。
「最後のaはそれ以降の文字を”スペース”を付加して抽出して加える」
この場合、 コードはどうなるでしょうか。
「defghabcaxyarsからaを指定してdbxa rsを得る」 ということです。

[addresshopeph]

“yambejp” さん回答の更新どうもありがとうございます。
追加してお伺いしたいことがあります。
セル内に不特定数ある特定文字を検索して、各々の次の1文字を連続して全て抽出する
「最後の特定文字aは次の1文字以降の文字を”スペース”を付加して抽出して加える」
この場合、 コードはどうなるでしょうか。
「defghabcaxyarsmnからaを指定してdbxr smnを得る」 ということです。

[yambejp]

ちょっと場当たり的になってきていますが、追記してあります
例外処理で条件分岐が必要になるような気もしますが、命題のとおりは動作すると思います

[addresshopeph]

うまく動作しました。
とてもわかりやすくご教授くださり助かりました。
真摯なご対応どうもありがとうございます。
今後もし更新することがございましたらよろしくお願いします。

[yambejp]

蛇足ですがユーザー関数はアドオンを作っておけば他の環境でも動作させることは可能です。
もしそのあたりご理解が及んでいないようであれば確認いただければ共有できると思います

[addresshopeph]

わかりました。
どうもありがとうございます。

Answer 2

解決済みですが、作成したので置いておきます。

「defghabcaxyapqからaを指定してdbxpを得る」

回答のコメント内の最初の仕様

Function extractNextCharacter(orgStr As String, searchStr As String) As String
    Dim i
    For Each i In Split(orgStr, searchStr)
        extractNextCharacter = extractNextCharacter & Left(i, 1)
    Next
End Function

「defghabcaxyarsからaを指定してdbxa rsを得る」

回答のコメント内の最後の仕様

「最後のaはそれ以降の文字を”スペース”を付加して抽出して加える」
この場合、 コードはどうなるでしょうか。

Function extractNextCharacterX(orgStr As String, searchStr As String) As String
    Dim a, i As Long
    a = Split(orgStr, searchStr)
    For i = LBound(a) To UBound(a) - 1
        extractNextCharacterX = extractNextCharacterX & Left(a(i), 1)
    Next
    extractNextCharacterX = extractNextCharacterX & searchStr & " " & a(i)
End Function

Comments

[addresshopeph]

ご回答どうもありがとうございます。
私が思い違いしていました。

①
defghabcaxyapqからaを指定してdbxを得る

②
defghabcaxyarsmnからaを指定してdbxr smnを得る
です。

誠に申し訳ございませんが、再度よろしくお願いします。

[hatena19]

①
Function extractNextCharacter1(orgStr As String, searchStr As String) As String
　　Dim a, i As Long
　　a = Split(orgStr, searchStr)
　　For i = LBound(a) To UBound(a) - 1
　　　　extractNextCharacter1 = extractNextCharacter1 & Left(a(i), 1)
　　Next
End Function

②
Function extractNextCharacterX1(orgStr As String, searchStr As String) As String
　　Dim a, i As Long
　　a = Split(orgStr, searchStr)
　　For i = LBound(a) To UBound(a) - 1
　　　　extractNextCharacterX1 = extractNextCharacterX1 & Left(a(i), 1)
　　Next
　　extractNextCharacterX1 = extractNextCharacterX1 & Left(a(i), 1) & " " & Mid(a(i), 2)
End Function

どちらにしても、私の回答のコードを少し改変するだけですので、
コードの意味を理解して、自分で応用して改変できるようにすべきでしょう。

[addresshopeph]

①はうまく動作しました。
②は私のExcelの環境下ではうまく動作しないようです。
回答どうもありがとうございました。

[hatena19]

当方の環境では動作確認済みです。
環境に依存するようなものは使っていないので、動作するはずです。
間違いないか、見直してみてください。
どちらにしてもコードを理解して応用が利くように頑張ってください。

[addresshopeph]

何回か確認しなおしたらうまく動作しました。
ご丁寧な回答助かりました。
VBAコードを理解して頑張ります。
どうもありがとうございました。