公式来查看一个姓氏是否在单元格内重复输出一个简化的字符串 [英] 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屋!