CODEWITHSUNDEEP
excelexcel-formula
asd213e1
asd213e1

Reputation: 43

Excel find and replace function correct formula

I wish to use the find and replace function in excel to remove example sentences from cells similar to this:

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

Sentences are in between 「」brackets and will include a ― and / character somewhere inside the brackets.
I have tried 「*―*/*」 however this will delete everything from the right of the〔
Is there any way to target and delete these specific sentence brackets, with the find and replace tool?

Desired outcome:

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

Upvotes: 1

Views: 307

Answers (4)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60354

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

enter image description here

Upvotes: 0

JvdV
JvdV

Reputation: 75930

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)))

enter image description here

Upvotes: 1

bosco_yip
bosco_yip

Reputation: 3802

Or,

In B1 enter formula :

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

enter image description here

Upvotes: 0

Dang D. Khanh
Dang D. Khanh

Reputation: 1471

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)) & "」","")

enter image description here

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,"")

Upvotes: 0

Related Questions