Excel假日期處理方法

某天，有同事找我，說不知道怎麼搞的，把工作表中的一列日期弄成了如下圖A列的日期樣式，不能正常運算和排序，無法統計數據，共有5000多行，已經保存無法撤銷。他說實在沒有勇氣手動一個一個把後面的英文刪除，太多了，估計要花幾個小時，所以來問我有沒有什麼高效的方法。

最容易想到的就是查找替換，先把星期一到星期日的英文替換為空操作7次，然後把單元格格式設置成標準日期格式，大家可以動手試下。

另外的方法就是公式，下面介紹三種公式：

方法一

B2=TEXT(SUBSTITUTE(A2,VLOOKUP(1,CHOOSE({1,2},N(FIND({"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday";"Sunday"},A2)>0),{"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday";"Sunday"}),2,),""),"e-m-d")

思路分析

替換法：構建一個兩列數組，用VLOOKUP查找返回單元格中存在的星期英文單詞，然後用SUBSTITUTE把星期英文替換為空；TEXT函數的作用是日期格式轉換。

方法二

C2=TEXT(LEFT(A2,LOOKUP(1,0/EXACT(UPPER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),LOWER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))),ROW(INDIRECT("1:"&LEN(A2))))),"e-m-d")

思路分析

提取法：根據只有字母在轉換大小寫時會生產變化，利用LOOKUP函數找到最小位置的字母與數字的分界位置，然後用LEFT函數提取到分界位置，最後用TEXT函數轉換日期格式。

方法三

D2=TEXT(LOOKUP(2^20,--LEFT(A2,ROW($1:$20))),"e-m-d")

思路分析

把字元從左到右，1個，2個，3個…..提取出來，統一在字元前加兩個負號，如果是日期或數字就返回數字，如果是文本就返回錯誤值。

再LOOKUP函數查找到最大的日期序列數，最後用TEXT函數完成日期格式轉換。

方法二和方法三用到了LOOKUP函數查找原理-二分法，其查找原理非常難以理解，以後再專門講解，會套用公式就行了。

如果還有其它方法，請在文章下方留言。

好了，今天的分享就到這裡，有疑問歡迎留言提問，謝謝大家！