查找字符串中的重复文本并将其删除

问题描述

我有这个excel的问题。我试图在2列之间找到匹配的文本，然后删除匹配的文本。示例

列1：

  John Romeo 
  

列2：

 约翰·罗密欧16史密斯街
  

结果：

  16史密斯街
  

结果列是文本我想要。

解决方案

这是一个名为WORDDIF的自定义函数，可以做你想要的。

要在Windows中安装自定义功能...
Alt + F11打开VBA编辑器
从VBA菜单中选择插入 - >模块

要在OS X中安装自定义函数...
转到工具 - >宏 - > Visual Basic编辑器
从VBA菜单中选择插入 - >模块

将代码粘贴到VBA编辑窗口中

返回Excel，将此公式复制到结果列中： p>

结果= WORDDIF（Column1Cell1，Column2Cell2）

 函数WORDDIF（rngA As Range，rngB As Range）As String 
 
 Dim WordsA As Variant，WordsB As Variant 
 Dim ndxA As Long，ndxB As Long，strTemp As String 
 
 WordsA = Split（rngA.Text，）
 WordsB = Split（rngB.Text，）
 
对于ndxB = LBound（WordsB）对于UBound（WordsB）
对于ndxA = LBound（WordsA）到UBound（WordsA）
如果StrComp（WordsA（ndxA），WordsB（ndxB），vbTextCompare）= 0然后
 WordsA（ndxA ）= vbNullString 
退出
结束如果
下一个ndxA 
下一个ndxB 
 
对于ndxA = LBound（WordsA）到UBound（WordsA）
如果WordsA（ndxA）<> vbNullString然后strTemp = strTemp& WordsA（ndxA）& 
 Next ndxA 
 
 WORDDIF = Trim（strTemp）
 
结束函数
  

对于归因，解决方案来自

I have this excel problem. I am trying to find matching text between 2 columns and then remove the matched text. Example

Column 1:

John Romeo

Column 2:

John Romeo 16 Smith Street

Results:

16 Smith Street

The Results column is the text that I want.

解决方案

Here's a custom function called WORDDIF that may do what you want.

To install the custom function in Windows ...Alt+F11 to open the VBA EditorFrom the VBA menu, select Insert -> Module

To install the custom function in OS X ...Go to Tools -> Macro -> Visual Basic EditorFrom the VBA menu, select Insert -> Module

Paste the code below in the VBA Edit window

Back in Excel, copy this formula into your Results column:

Results=WORDDIF(Column1Cell1, Column2Cell2)

Function WORDDIF(rngA As Range, rngB As Range) As String

Dim WordsA As Variant, WordsB As Variant
Dim ndxA As Long, ndxB As Long, strTemp As String

WordsA = Split(rngA.Text, " ")
WordsB = Split(rngB.Text, " ")

For ndxB = LBound(WordsB) To UBound(WordsB)
    For ndxA = LBound(WordsA) To UBound(WordsA)
        If StrComp(WordsA(ndxA), WordsB(ndxB), vbTextCompare) = 0 Then
            WordsA(ndxA) = vbNullString
            Exit For
        End If
    Next ndxA
Next ndxB

For ndxA = LBound(WordsA) To UBound(WordsA)
    If WordsA(ndxA) <> vbNullString Then strTemp = strTemp & WordsA(ndxA) & " "
Next ndxA

WORDDIF = Trim(strTemp)

End Function

For attribution, the solution is from http://www.mrexcel.com/forum/excel-questions/486708-compare-two-strings-find-difference.html

这篇关于查找字符串中的重复文本并将其删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！