同一个格内重复的数字只保留一个
要求在表里边，先谢如果只有一个重复
=REPLACE(A1,MIN(IF(ROUNDUP(FIND(MID(A1,{1,2,3,4,5}*4-3,3),A1)/4,)={1,2,3,4,5},9,{1,2,3,4,5}))*4-3,4,"")
多个重复
=TEXTJOIN(" ",,TEXT(FILTERXML("<a><b>"&SUBSTITUTE(A1," ","</b><b>")&"</b></a>","a/b"),"000"))
365
=TEXTJOIN(" ",,UNIQUE(MID(A1,{1;2;3;4;5}*4-3,3))) =TEXTJOIN(" ",,UNIQUE(TRIM(MID(A1,SORT(UNIQUE(IFERROR(FIND(" ",A1,ROW(INDIRECT("1:"&LEN(A1)))),0)))+1,4)))) 低版本，多重复的
=SUBSTITUTE(TEXT(SUM(LARGE(IF(MMULT(N(--MID(A1,{1,2,3,4,5}*4-3,3)=ROW($1:$99)),{1;1;1;1;1}),ROW($1:$99),),ROW($1:$5))*1000^(ROW($1:$5)-1)),REPT("000 ",5)),"000 ",) 拆分、去重、再合并：
=TEXTJOIN(" ",,UNIQUE(SSPLIT(A1)))
Public Function SSPLIT(ByVal strText$, Optional ByVal Delimiter = " ", Optional ByVal Limit& = -1, Optional ByVal Compare As VBA.VbCompareMethod = vbBinaryCompare)
SSPLIT = VBA.Split(strText, Delimiter, Limit, Compare)
End Function
Public Function TEXTJOIN(ByVal Delimiter, ByVal ignore_empty, ParamArray text()) As String
Dim tmptext As Variant, rng As Variant, cel As Variant, i As Long
If IsMissing(Delimiter) Then Delimiter = "" Else Delimiter = CStr(Delimiter)
If IsMissing(ignore_empty) Then ignore_empty = True Else ignore_empty = CBool(ignore_empty)
tmptext = ""
For i = 0 To UBound(text)
rng = text(i)
If IsArray(rng) Then
For Each cel In rng
If Len(cel) Or Not ignore_empty Then tmptext = tmptext & Delimiter & cel
Next cel
Else
If Len(rng) Or Not ignore_empty Then tmptext = tmptext & Delimiter & rng
End If
Next
TEXTJOIN = Mid(tmptext, Len(Delimiter) + 1)
End Function
Public Function UNIQUE(ParamArray rng())
Dim dic As Object, itm, ocel, skey
Set dic = CreateObject("scripting.dictionary")
For Each itm In rng
If TypeName(itm) = "Range" Then
For Each ocel In itm
dic(ocel.Value) = ""
Next
Else
If IsArray(itm) Then
For Each skey In itm
dic(skey) = ""
Next
Else
dic(itm) = ""
End If
End If
Next
UNIQUE = dic.keys
End Function =CONCAT(IF(FIND(MID(A1,{1;5;9;13;17},3),A1)={1;5;9;13;17},MID(A1,{1;5;9;13;17},4),""))
页:
[1]