Excelの複数置換はSUBSTITUTEを重ねるしかない？

Excelのデータクレンジング作業で、
・半角スペースを削除
・”字”を削除
・改行コードを削除
・”ｹ”を”ケ”に置換
等、複数の置換が必要なのですが、
一つ一つ、複数のSUBSTITUTEでくくりながら長い数式を書くしかないでしょうか？
複数の置換をスマートに実施する良い方法があれば押しせてください。

No.6 ベストアンサー 回答者： d-q-t-p 回答日時： 2022/05/10 14:19

> 具体的にどのようにやるんでしょうか？

置換の対象が「大字」みたいな２文字以上の文字列を含むかどうかで
全く違ってきます。

元のデータを「テーブル1」
置換用を「置換テーブル」
として読み込んであるものとして

let
置換テーブル = #table(type table[前=text, 後=text],{{" ",""},{"字",""},{"#(lf)",""},{"ｹ","ケ"}}),
テーブル1 = #table(type table[名前=text], {{"あいう かきく さしす"},{"あいう えお字かきく"},{"あいうえお#(lf)かきくけこ"},{"あいうｹえこｹさし"}}),
値の置換 = Table.AddColumn(テーブル1, "変換後",
each List.Reverse(
List.Generate(
()=> [i=0, name=[名前]],
each [i] <= Table.RowCount(置換テーブル),
each [
i = [i]+1,
name = Text.Replace([name],置換テーブル{i-1}[前],置換テーブル{i-1}[後])
],
each [name]
)
){0},
type text
)
in
値の置換

１文字→１文字の置換でよければ Text.Splitで分割したものを置換して
から結合しなおせばいいです。

この回答へのお礼

ありがとうございます。ややこしいですね・・・
後程解読します(笑

お礼日時：2022/05/10 16:16

No.9 回答者： Belltreeriver 回答日時： 2022/05/17 08:21

NO.8です。

式の一部にふさわしくない部分がありました。
LEN(A6)+1　⇒　ROWS($D$3:$D$6) +1
と修正します。

No.8 回答者： Belltreeriver 回答日時： 2022/05/17 00:35

こんばんは

当方Microsoft365ではなく2019のため、
No.7さんのレイアウトで配列数式版で考えてみました。
置換後はテーブルではなく、CHOOSE関数を使用している関係上、
$E$3,$E$4,$E$5,$E$6とダイレクトにセル指定する必要があります。
置換後の空欄セルは　="" でセットしてください。
ご参考です。
{=CONCAT(
IFERROR(
CHOOSE(
IFERROR(
MATCH(MID(A6,ROW(OFFSET($A$1,,,LEN(A6))),1),$D$3:$D　　
　　　　$6,0),
LEN(A6)+1),
$E$3,$E$4,$E$5,$E$6),
MID(A6,ROW(OFFSET($A$1,,,LEN(A6))),1))
)}

この回答へのお礼

ご親切にありがとうございます。
順番に試してみたいと思います

お礼日時：2022/05/28 21:19

No.7 回答者： d-q-t-p 回答日時： 2022/05/10 14:30

Microsoft365みたいなので一応 数式も載せておきます。

画像右のように置換テーブルを作成しておいて

B3に
=LET(_rng,[@名前],_rws,ROWS(置換テーブル[前]),INDEX(SCAN([@名前],SEQUENCE(_rws),LAMBDA(a,b,SUBSTITUTE(a,INDEX(置換テーブル[前],b),INDEX(置換テーブル[後],b)))),_rws))

この回答へのお礼

更に具体的にありがとうございます！
実は週末にまた作業が発生しそうなのでそこで活用したいと思います。

お礼日時：2022/05/10 16:17

No.5 回答者： d-q-t-p 回答日時： 2022/05/09 12:41

> 一つ一つ、複数のSUBSTITUTEでくくりながら長い数式を書くしか

> ないでしょうか？
「数式でやる場合」ですか？　バージョンによります。
LAMBDA関数が使えるなら再帰処理が可能です。
古いバージョンの場合は循環参照で反復計算させればできます。
個人的にはお勧めしません。

マクロでやれば当然できますが 置換の対応表があるならクエリでやっ
てもいいように思います。

この回答へのお礼

ありがとうございます。
こちらOffice365なので最新です。
「LAMBDA関数」最近別件で耳にしましたので興味があります。
しかし本件はお勧めしないやり方なんですね。。。

> 置換の対応表があるならクエリで
具体的にどのようにやるんでしょうか？

・半角スペースを削除
・”字”を削除
・改行コードを削除
・”ｹ”を”ケ”に置換
こういった複数条件を対応表にして実現できますか？

お礼日時：2022/05/09 14:59

No.4 回答者： taglet 回答日時： 2022/05/08 15:10

VBAで以下のようなコードを書くと、置換ができます。

Sub Main

Dim MotoText() As Variant, SakiTest() as Variant
Dim i As Integer

MotoText = Array(" ", "字", Chr(10), "ｹ")
SakiText = Array("", "", "", "ケ")

Range("B1:B10") = Range("A1:A10")

For i = 0 To UBound(MotoText)
Range("B1:B10").Replace What:=MotoText(i), replacement:=SakiText(i)
Next i

End Sub

この回答へのお礼

ありがとうございます。その方法だと複数の条件を併記すれば拡張性ありますね。
ネックと言えば一度実行すると履歴を辿って元に戻せない事ですね。
方法を考えて活用したいと思います。

お礼日時：2022/05/09 14:57

No.3 回答者： fujillin 回答日時： 2022/05/08 13:24

こんにちは

置換の組み合わせの種類がいろいろあるような場合は、あまり有効ではありませんけれど・・

組み合わせ種類が少なければ、ユーザ定義関数にしておいて、それを利用するとか。
やっている内容は変わらないので処理としてはメリットはありませんけれど、セル内の関数式を単純化できるというメリットはあるでしょう。

No.2 回答者： mygoonickname 回答日時： 2022/05/08 12:29

No.1の者です。

では、隣の列に元データをコピーして、それを置換してはどうでしょうか？
元データはそのままで、コピーした方のデータだけ置換するで。

No.1 回答者： mygoonickname 回答日時： 2022/05/08 11:39

こんにちは。

元データを修正して良いなら、関数ではなく、Ctrl+h の置換では、
ダメなのでしょうか？

若しくは、マクロで処理するとか？

この回答へのお礼

駄目なんです。
オリジナルに手を付けてはいけません。
別データとの突合用にクレンジングします。
1件1件やる作業ではないので、隣のセルにクレンジング結果が表示される必要があります。
ボタンを押したら隣の空き列に貼り付けられるマクロがあれば良いのですが。。。

お礼日時：2022/05/08 12:26