エクセルでセルにある文字列が含まれる場合の文字置き換えを複数条件で行う方法

エクセルで
セルに指定した文字列が含まれている場合
別の文字列に置き換えることをしたいです。

countifやfind関数を使えばできるのですが
複数条件でしたいのです。

複数条件にするとうまくいきません。

例えば
Ａ１の文字列に島根という文字がある場合はshimane,
Ａ１の文字列に東京という文字がある場合はtokyo
Ａ１の文字列に大阪という文字がある場合はosaka

その答えをA2のセルに入れる

というようにしたいです。

申し訳ありませんが　いい方法はないでしょうか

No.1 回答者： tom04 回答日時： 2018/06/20 10:38

こんにちは！

＞指定した文字列が含まれている場合
一例です。

↓の画像のD・E列のような対応表を作成しておきます。
アスタリクス「*」を入れているのは数式で空白セルを範囲指定しないようにするためで、
今後検索文字列が増える場合はアスタリクスの行に追加していきます。

画像ではB1セルに
=IF(OR(ISNUMBER(FIND(D$1:D$10,A1))),INDEX(E$1:E$10,SUMPRODUCT((ISNUMBER(FIND(D$1:D$10,A1)))*ROW(D$1:D$10))),"")

配列数式なので、Ctrl＋Shift＋Enterで確定！（←必須★）し
フィルハンドルで下へコピーしています。

※　セル内に複数の検索文字は含まれない！という前提です。m(_ _)m

この回答へのお礼

丁寧にありがとうございます、

試しに簡単な例ですれば　うまくいくのですが
私が使いたい文章だと　なぜか　うまくいかなったです。

お礼日時：2018/06/20 19:23

No.2 回答者： GooUserラック 回答日時： 2018/06/20 11:21

ちなみに「大阪東京島根」だったらどう表示するのですか？

この回答へのお礼

ありがとうございます。

想定として、スペースなく連続はないので
問題ないと思います。

お礼日時：2018/06/20 19:24

No.3 回答者： fujillin 回答日時： 2018/06/20 11:28

こんにちは

含まれている文字はどれか一つと限定してもよいのならば比較的簡単ですが、複数の対象が含まれている場合も全部変換するとなると少々面倒そうですね。

例えば
『東京から大阪へ、そして島根へ』を一気に『tokyoからosakaへ、そしてshimaneへ』と変換したいという意味でしょうか？

ひとつの方法として、作業列を用いて分解して順に処理してゆくのが簡単な方法だと思います。
・B1へは島根→shimaneの変換結果を
・B1セルの文字列に対して、C1セルに東京→tokyoの変換結果を
・C1セルの文字列に対して、D1セルに大阪→osakaの変換結果を
というような手順です。
上記では説明の都合上最終の文字列がD1セルになっていますが、これをB1セルにして中間結果の文字列（上記ではB1セル、C1セル）をどこか別のセルに設定しておけば、見かけ上はB1セルに最終の変換結果が示されるようになります。（中間結果のセル（列）は非表示にしておくなどで見えなくすることも可能です）

上記の関数を全部組み合わせれば、一気に変換する式を作成することも可能ですが、式がとたんに長くなります。
（項目が一つ増えるごとに、約3倍強の長さになっていく感じです）
試みに、ご提示の3項目の例で一気に全部変換できる式を作成してみると
B1セルに

=IFERROR(REPLACE(IFERROR(REPLACE(IFERROR(REPLACE(A1,FIND("島根",A1),2,"shimane"),A1),FIND("東京",IFERROR(REPLACE(A1,FIND("島根",A1),2,"shimane"),A1)),2,"tokyo"),IFERROR(REPLACE(A1,FIND("島根",A1),2,"shimane"),A1)),FIND("大阪",IFERROR(REPLACE(IFERROR(REPLACE(A1,FIND("島根",A1),2,"shimane"),A1),FIND("東京",IFERROR(REPLACE(A1,FIND("島根",A1),2,"shimane"),A1)),2,"tokyo"),IFERROR(REPLACE(A1,FIND("島根",A1),2,"shimane"),A1))),2,"osaka"),IFERROR(REPLACE(IFERROR(REPLACE(A1,FIND("島根",A1),2,"shimane"),A1),FIND("東京",IFERROR(REPLACE(A1,FIND("島根",A1),2,"shimane"),A1)),2,"tokyo"),IFERROR(REPLACE(A1,FIND("島根",A1),2,"shimane"),A1)))

のような式を入力することで可能と思います。

※ 上の式でも同じものが複数回出現する場合には対応していません。
例えば、
『東東京と西東京では、どちらがより東京らしいのか？』
のような文字列です。
このようなものも含めて対応なさりたい場合は、ユーザ定義関数にしてしまった方が簡単に実現できますね。

No.4 回答者： mike_g 回答日時： 2018/06/20 12:55

[No.1]の tom04さんの回答、他のケースにも応用できそうで、感心いたしました。

そこで、添付図を借用して別解を考えたので、参考までに。
B1: =INDEX(E$1:E$10,MATCH(1,0/FIND(D$1:D$10,A1)))
【お断り】上式は必ず配列数式として入力のこと

この回答へのお礼

ありがとうございます。

教えてもらった方法だと　私の使い方では
うまくいきます。

使わせてもらいます。

お礼日時：2018/06/20 19:25

No.5 回答者： GooUserラック 回答日時： 2018/06/20 13:05

No.2 の補足

「大阪から東京を経由して島根に戻る」の場合、次のどれでしょうか？
①「shimane」（リストの上から最初にヒットした物だけ表示する）
②「osaka」（一番最初にヒットした物だけを表示する）
③「shimanetokyoosaka」（リストの上からヒットした順に表示する）
④「osakatokyoshimane」（ヒットした順に表示する）
⑤ その他（具体的に第３者が判るように説明してください）

No.6 回答者： mike_g 回答日時： 2018/06/20 17:11

motoentaroさん、

確認させてください。
重箱の隅を突くわけではないけど、参照すべきリストに tokyo、kyoto、osaka、… が登録されているとき、「Ａ１の文字列」が「私の住まいは東京都です」のときはどうしますか？
東京と京都が重なり合っているように、Excelさんは戸惑うに違いないのです。

この回答へのお礼

ありがとうございます。

東京は例として　ここでださせてもらいましたので
実際の使用では　ありません。

ですので、考えていませんが　その時は　どうしましょう。

お礼日時：2018/06/20 19:26

No.7 回答者： tom04 回答日時： 2018/06/20 19:44

No.1です。

他の方々からもご指摘があるように1セル内に重複する場合でも対応する方法にしてみました。
（東京都は東京と京都が重複）

VBAになりますが一例です。
今回は↓の画像のようにD・E列は必要数だけ入れておきます。

Sub Sample1()
　Dim i As Long
　Dim FoundCell As Range, FirstCell As Range
　　Range("B:B").ClearContents
　　　For i = 1 To Cells(Rows.Count, "D").End(xlUp).Row
　　　　Set FoundCell = Range("A:A").Find(what:=Cells(i, "D"), LookIn:=xlValues, lookat:=xlPart)
　　　　　If Not FoundCell Is Nothing Then
　　　　　　GoTo 処理
　　　　　　Set FirstCell = FoundCell
　　　　　　Do
　　　　　　　Set FoundCell = Range("A:A").FindNext(after:=FoundCell)
　　　　　　　　If FoundCell.Address = FirstCell.Address Then Exit Do
　　　　　　　　GoTo 処理
　　　　　　Loop
処理:
　　　　　　With FoundCell.Offset(, 1)
　　　　　　　If .Value = "" Then
　　　　　　　　.Value = Cells(i, "E")
　　　　　　　Else
　　　　　　　　.Value = .Value & "," & Cells(i, "E")
　　　　　　　End If
　　　　　　End With
　　　　　End If
　　　　Next i
End Sub

これで重複があっても1セル内に表示されます。
※　当然重複がなくても大丈夫です。m(_ _)m

No.8 ベストアンサー 回答者： tom04 回答日時： 2018/06/20 20:18

No.1・7です。

何度もごめんなさい。
すでに解決済みかと思いますが、前回のコードが間違っていました。
興味があれば↓のコードに変更してみてください。

Sub Sample2()
　Dim i As Long
　Dim FoundCell As Range, FirstCell As Range
　　Range("B:B").ClearContents
　　　For i = 1 To Cells(Rows.Count, "D").End(xlUp).Row
　　　　Set FoundCell = Range("A:A").Find(what:=Cells(i, "D"), LookIn:=xlValues, lookat:=xlPart)
　　　　　If Not FoundCell Is Nothing Then
　　　　　　　Set FirstCell = FoundCell
　　　　　　　GoTo 処理
　　　　　　Do
　　　　　　　Set FoundCell = Range("A:A").FindNext(after:=FoundCell)
　　　　　　　　If FoundCell.Address = FirstCell.Address Then Exit Do
　　　　　　　　GoTo 処理
処理:
　　　　　　　With FoundCell.Offset(, 1)
　　　　　　　　If .Value = "" Then
　　　　　　　　　.Value = Cells(i, "E")
　　　　　　　　Else
　　　　　　　　　.Value = .Value & "," & Cells(i, "E")
　　　　　　　　End If
　　　　　　　End With
　　　　　　Loop
　　　　　End If
　　　　Next i
End Sub

※　セルの配置は前回アップした画像通りだとします。
どうも失礼しました。m(_ _)m