excel 中一個單元中,怎樣去掉重復的數(shù)字

2024-02-20 11:32:54 241次

問題描述：

excel 中一個單元中,怎樣去掉重復的數(shù)字希望能解答下

其他答案

馮程

從事留學10年以上，幫助過很多的國內學生處理留學申請，簽證，生活，...

2024-02-20 11:32:54

才開始沒有看清題目,修改了公式.另外寫了一個自定義函數(shù)。1.=LEFT(IF(ISERR(FIND(0,A1)),"",0)&IF(ISERR(FIND(1,A1)),"",1)&IF(ISERR(FIND(2,A1)),"",2)&IF(ISERR(FIND(3,A1)),"",3)&IF(ISERR(FIND(4,A1)),"",4)&IF(ISERR(FIND(5,A1)),"",5)&IF(ISERR(FIND(6,A1)),"",6)&IF(ISERR(FIND(7,A1)),"",7)&IF(ISERR(FIND(8,A1)),"",8)&IF(ISERR(FIND(9,A1)),"",9),4)A1中前4個出現(xiàn)的數(shù)字--------------------2.=SUBSTITUTE(CONCATENATE(IF(ISERR(FIND(0,B1)),0),IF(ISERR(FIND(1,B1)),1),IF(ISERR(FIND(2,B1)),2),IF(ISERR(FIND(3,B1)),3),IF(ISERR(FIND(4,B1)),4),IF(ISERR(FIND(5,B1)),5),IF(ISERR(FIND(6,B1)),6),IF(ISERR(FIND(7,B1)),7),IF(ISERR(FIND(8,B1)),8),IF(ISERR(FIND(9,B1)),9)),FALSE,"")B1中沒有出現(xiàn)的數(shù)字-------------自定義函數(shù)GetAppear代碼如下:***********************************Function GetAppear(Rng As Range, Str As String, Optional Tpye As Boolean = True)Dim Arr() As StringDim I As IntegerReDim Arr(1 To Len(Str))For I = 1 To Len(Str)Select Case TpyeCase True:If InStr(Rng, Mid(Str, I, 1)) ThenArr(I) = Mid(Str, I, 1)End IfCase False:If InStr(Rng, Mid(Str, I, 1)) = 0 ThenArr(I) = Mid(Str, I, 1)End IfEnd SelectNextGetAppear = Replace(Join(Arr), " ", "")End Function***********************************使用方法:Alt+F11,菜單,插入-模塊復制以上代碼?；氐紼xcelA1的內容是:1334557B1輸入公式:=Left(GetAppear(A1,"0123456789",True),4)返回A1中0123456789出現(xiàn)的數(shù)字前4個數(shù)字1345C1輸入公式:=GetAppear(A1,"0123456789",False)返回B1中0123456789沒有出現(xiàn)的數(shù)字026789這個自定義函數(shù)的適用范圍可以更廣一點