Help us understand the problem. What is going on with this article?

ワイルドカード的な置換処理①区切り文字間の文字

Power Queryを使っていて、ワイルドカードがあったらな、と思うことがあります。
ワイルドカードそのものは実現できないものの、白紙の状態から都度関数を書くのも辛いなと思っていて、その材料を書き留めておこうと思います。

お題

指定の区切り文字に囲われた中身を、一律に別の値に置換するというもの。
例えば、
(x)(y)(z)(x)
のような文字列を、
(a)(a)(a)(a)
というように。
Excelの置換でやると、こんな感じのこと。
image.png

コード

関数化しています。

fx_replaceBetweenDelimiter
(Source as text,startDelimiter as text,endDelimiter as text,new as text)=>
let
    Lists =List.Generate(
                ()=>[Txt=Source,Start=0,Cnt=0,Between=null],
                each List.AllTrue({[Start]<>-1,[Between]<>""}),
                each [Between =Text.BetweenDelimiters(Source,startDelimiter,endDelimiter,[Cnt],0),
                      SubTxt  =startDelimiter & Between & endDelimiter,
                      Start   =Text.PositionOf([Txt],SubTxt),
                      NewTxt  =startDelimiter & new & endDelimiter,
                      Txt     =Text.ReplaceRange([Txt],Start,Text.Length(SubTxt),NewTxt),
                      Cnt     =[Cnt]+1
                     ],
                each [Txt]
            )
in
    List.Last(Lists)

使用例

image.png

感想

改めて、Excel、Wordの置換、ワイルドカードの偉大さを実感。
ただ、Power Queryの処理中にテーブルとかに出して、置換して再読み込みというのも嫌だなと思っていて、今にいたります。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした