公式来查看一个姓氏是否在单元格内重复输出一个简化的字符串 [英] formula to see if a surname is repeated within a cell and output a simplified string

问题描述

我有一个名单，其中包括几个不同的名字，如下所示：

 汤普森先生;汤普森太太汤普森先生
 C Guy-Johnson先生顾伟强夫人朱国强小姐
富勒先生D Fuller女士Dr. K U Fuller 
 Dr V Patel& Oe Patel博士
 Mr B Burden&伍德先生C Pollock女士
 Philips&飞利浦夫人
 Dr D. Watson& S Holmes 
 Mr R Polanski& S Polanski夫人
史必贝尔伯格先生斯皮尔伯格小姐Mrs T Spielberg 
  

有时姓名在单元格内重复，有时不是。

我想建立一个公式，以确定姓氏是否重复，并返回一个字符串，其中Salutations /标题和起始字符与姓氏连接在一起，除非姓氏是

例如，

   - 斯皮尔伯格先生&斯皮尔伯格小姐Mrs T Spielberg 
  -  R Polanski先生Mrs S Polanski 
  

将成为

   -  S& G& G小姐史培林夫人
  -  R& S波兰斯基夫人
  

但是：

   -  B Burden先生伍德先生C Pollock女士
  -  Dr D. Watson& S Holmes 
  

将保持与姓氏不同

可以用公式（而不是使用文本到列分割名称）来做到这一点，我该怎么做？

谢谢
Philip

解决方案

我相信Barry或Lori会想出一个聪明的公式:)但是这里是一个VBA例如，可能只是解决你老板的呼吸问题;）

将此代码粘贴到模块中。 （仅使用下面的截图中的示例进行测试）。我自由地操纵一个单元格值来考虑姓氏中的多个匹配项。参见单元格 A1

 函数GetNewNames（rng As Range）As String 
 Dim MyAr（）As String，tmpAr（）As String 
 Dim prevValue As String，sTmp As String，surName As String，sTemp As String 
 Dim i As Long 
 Dim col As新集合
 Dim itm As Variant 
 
错误GoTo Whoa：
 
如果不是rng是没有，然后
 prevValue = rng.Value 
 
如果InStr（1，prevValue，&）然后
 MyAr = Split（prevValue，&）
 
对于i = 0 To UBound（MyAr）
 sTmp = Trim（MyAr（i））
如果InStr（1，sTmp，）然后
 tmpAr = Split（sTmp，）
 surName = tmpAr（UBound tmpAr））
 Else 
 surName = sTmp 
结束如果
 
关于错误简历Next 
 col.Add surName，Chr（34）& surName& Chr（34）
在错误恢复下一个
下一个i 
 
对于每个itm在col 
对于i = 0到UBound（MyAr）
 sTmp = Trim（MyAr（i））
 
如果InStr（1，sTmp，）然后
 tmpAr = Split（sTmp，）
 surName = tmpAr（UBound（tmpAr ））
 Else 
 surName = sTmp 
如果
 
如果surName = itm然后
如果sTemp =然后
 sTemp = Trim （MyAr（i））
 Else 
 sTemp = Replace（sTemp&&& Trim（MyAr（i）），itm&&，&）
 End If 
 End If 
 Next i 
 Next 
 
 GetNewNames = sTemp 
 Else 
 GetN ewNames = prevValue 
 End If 
 End If 
退出函数
哇：
 GetNewNames =
结束函数
  

截图

I have a list of several thousand items, which consist of several different names together like this:

Mr P Thompson & Mrs S Thompson & Mr A Thompson
Mr C Guy-Johnson & Mrs A Guye-Johnson & Miss J Guye-Johnson
Mrs Fuller & Ms D Fuller & Dr K U Fuller
Dr V Patel & Dr OO Patel
Mr B Burden & Mr MP Wood & Ms C Pollock
Mr PW Philips & Mrs PW Philips
Dr D Watson & S Holmes
Mr R Polanski & Mrs S Polanski
Mr S Spielberg & Miss G Spielberg & Mrs T Spielberg

Sometimes the surname is repeated within the cell, sometimes it is not.

I want to build a formula that will determine if the surname is repeated, and return a string where the Salutations/titles and inititals are concatenated with the Surname at the end, unless the surnames are different.

For example,

- Mr S Spielberg & Miss G Spielberg & Mrs T Spielberg
- Mr R Polanski & Mrs S Polanski

would become,

- Mr S & Miss G & Mrs T Spielberg
- Mr R & Mrs S Polanski

BUT:

- Mr B Burden & Mr MP Wood & Ms C Pollock
- Dr D Watson & S Holmes

would remain the same as the surnames are different

Is it possible to do that with formulas, (and not splitting the names using Text to Columns), and how would I do that please?

thanks Philip

解决方案

I am sure Barry or Lori would come up with a smart formula :) However here is a VBA example which might just solve your boss's breathing problem ;)

Paste this code in a module. (Tested only with the samples in the screenshot below). I took the liberty to manipulate one of the cell values to take into consideration multiple matches in surnames. See Cell A1

Function GetNewNames(rng As Range) As String
    Dim MyAr() As String, tmpAr() As String
    Dim prevValue As String, sTmp As String, surName As String, sTemp As String
    Dim i As Long
    Dim col As New Collection
    Dim itm As Variant

    On Error GoTo Whoa:

    If Not rng Is Nothing Then
        prevValue = rng.Value

        If InStr(1, prevValue, "&") Then
            MyAr = Split(prevValue, "&")

            For i = 0 To UBound(MyAr)
                sTmp = Trim(MyAr(i))
                If InStr(1, sTmp, " ") Then
                    tmpAr = Split(sTmp, " ")
                    surName = tmpAr(UBound(tmpAr))
                Else
                    surName = sTmp
                End If

                On Error Resume Next
                col.Add surName, Chr(34) & surName & Chr(34)
                On Error Resume Next
            Next i

            For Each itm In col
                For i = 0 To UBound(MyAr)
                    sTmp = Trim(MyAr(i))

                    If InStr(1, sTmp, " ") Then
                        tmpAr = Split(sTmp, " ")
                        surName = tmpAr(UBound(tmpAr))
                    Else
                        surName = sTmp
                    End If

                    If surName = itm Then
                        If sTemp = "" Then
                            sTemp = Trim(MyAr(i))
                        Else
                            sTemp = Replace(sTemp & " & " & Trim(MyAr(i)), itm & " &", "&")
                        End If
                    End If
                Next i
            Next

            GetNewNames = sTemp
        Else
            GetNewNames = prevValue
        End If
    End If
    Exit Function
Whoa:
    GetNewNames = ""
End Function

Screenshot

这篇关于公式来查看一个姓氏是否在单元格内重复输出一个简化的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！