Excel查找和替換函數正確公式 (Excel find and replace function correct formula)

問題描述

Excel查找和替換函數正確公式 (Excel find and replace function correct formula)

我希望使用 excel 中的查找和替換功能從類似於以下的單元格中刪除例句：

text <br>〔「text」text，「text」text〕<br>（１）text「sentence―sentence/sentence」<br>（２）text「sentence―sentence」

句子在「」括號之間，並且在某處包含一個 ― 和 / 字符
我試過「*―*/*」 但是這會刪除〔
右邊的所有內容有沒有辦法定位和刪除這些特定的句子括號，使用查找和替換工具？

期望的結果：

text <br>〔「text」text，「text」text〕<br>（１）text<br>（２）text「sentence―sentence」

參考解法

方法 1:

Quite a long formula but in Excel O365 you could use:

=SUBSTITUTE(CONCAT(FILTERXML("<t><s>"&SUBSTITUTE(CONCAT(IF(MID(A1,SEQUENCE(LEN(A1)),1)="「","</s><s>「",IF(MID(A1,SEQUENCE(LEN(A1)),1)="」","」</s><s>",MID(A1,SEQUENCE(LEN(A1)),1)))),"<br>","|$|")&"</s></t>","//s[not(contains(., '「') and contains(., '―') and contains(., '/') and contains(., '」'))][node()]")),"|$|","<br>")

As long as you have access to CONCAT you could also do this in Excel 2019 but you'll have to swap SEQUENCE(LEN(A1)) for ROW(A$1:INDEX(A:A,LEN(A1)))

方法 2:

This formula won't work in many cases, but if the string has matching rules as in your example, then try this:

=SUBSTITUTE(C5,"「" & INDEX(TRIM(MID(SUBSTITUTE(","&SUBSTITUTE(C5,"」","「"),"「",REPT(" ",99)),(ROW(A1:INDEX(A1:A100,LEN(C5)‑LEN(SUBSTITUTE(C5,"」",""))))*2‑1)*99,99)),MATCH("*―*/*",TRIM(MID(SUBSTITUTE(","&SUBSTITUTE(C5,"」","「"),"「",REPT(" ",99)),(ROW(A1:INDEX(A1:A100,LEN(C5)‑LEN(SUBSTITUTE(C5,"」",""))))*2‑1)*99,99)),0)) & "」","")

explain how it works:

1. split the string between the characters "「 "and "」" into an array
2. use match("*―*/*",,0) to find the string position (note that it will only return one value if it exists, if you have multiple strings, you can replace match("*―*/*",) with search ("*―*/*",..) and use it as an extra column to get matches string)
3. Use the index(array,match("*―*/*",..)) to get the string needs replacing (result)
4. Replace the original string with the results found =substitute(txt,result,"")

方法 3:

Or,

In B1 enter formula :

=SUBSTITUTE(A1,"「"&TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("」",A1,FIND("/",A1))),"「",REPT(" ",99)),99)),"")

方法 4:

You did not tag [VBA], but if you are not averse, you could write a User Defined Function that would do what you want using Regular Expressions.

To enter this User Defined Function (UDF), alt‑F11 opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window. Then, from the top menu, select Insert/Module and paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like =replStr(A1) in some cell.

Option Explicit
Function replStr(str As String) As String
    Dim RE As Object
    Const sPat As String = "\u300C(?:(?=[^\u300D]*\u002F)(?=[^\u300D]*\u2015)[^\u300D]*)\u300D"

Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .Pattern = sPat
    replStr = .Replace(str, "")
End With
End Function

(by asd213e1、JvdV、Dang D. Khanh、bosco_yip、Ron Rosenfeld)

參考文件

1. Excel find and replace function correct formula (CC BY‑SA 2.5/3.0/4.0)