如何在Excel中快速將日期轉換為單詞

發布:未知文本批量工具

如何在Excel中快速將日期轉換為單詞？

通常，我們通常在Excel中將日期轉換為其他日期格式或數字，但是您是否遇到過將日期轉換為英語單詞的問題，如下圖所示？實際上，沒有內置功能可以處理它，而是VBA代碼。

使用定義的函數將日期轉換為單詞

這是一個宏代碼，可以幫助您將日期轉換為單詞。

1.啟用您使用的工作表，然后按 Alt + F11鍵 打開鑰匙 Microsoft Visual Basic應用程序 窗口。

2。點擊插頁 > 模塊并將以下代碼粘貼到腳本中。

VBA：將日期轉換為單詞

Function DateToWords(ByVal xRgVal As Date) As StringUpdatebyExtendoffice20170926Dim xYear As StringDim Hundreds As StringDim Decades As StringDim xTensArr As VariantDim xOrdArr As VariantDim xCardArr As VariantxOrdArr = Array("First", "Second", "Third", _"Fourth", "Fifth", "Sixth", _"Seventh", "Eighth", "Nineth", _"Tenth", "Eleventh", "Twelfth", _"Thirteenth", "Fourteenth", _"Fifteenth", "Sixteenth", _"Seventeenth", "Eighteenth", _"Nineteenth", "Twentieth", _"Twenty-first", "Twenty-second", _"Twenty-third", "Twenty-fourth", _"Twenty-fifth", "Twenty-sixth", _"Twenty-seventh", "Twenty-eighth", _"Twenty-nineth", "Thirtieth", _"Thirty-first")xCardArr = Array("", "One", "Two", "Three", "Four", _"Five", "Six", "Seven", "Eight", "Nine", _"Ten", "Eleven", "Twelve", "Thirteen", _"Fourteen", "Fifteen", "Sixteen", _"Seventeen", "Eighteen", "Nineteen")xTensArr = Array("Twenty", "Thirty", "Forty", "Fifty", _"Sixty", "Seventy", "Eighty", "Ninety")xYear = CStr(Year(xRgVal))Decades = Mid$(xYear, 3)If CInt(Decades) < 20 ThenDecades = xCardArr(CInt(Decades))ElseDecades = xTensArr(CInt(Left$(Decades, 1)) - 2) & "-" & _xCardArr(CInt(Right$(Decades, 1)))End IfHundreds = Mid$(xYear, 2, 1)If CInt(Hundreds) ThenHundreds = xCardArr(CInt(Hundreds)) & " Hundred "ElseHundreds = ""End IfDateToWords = xOrdArr(Day(xRgVal) - 1) & _Format$(xRgVal, " mmmm ") & _xCardArr(CInt(Left$(xYear, 1))) & _" Thousand " & Hundreds & DecadesEnd FunctionCopy

3.保存代碼并返回到工作表，選擇一個要在其中輸出結果的單元格，鍵入此公式 = DateToWords（A1）（A1是您使用的日期），按輸入

鍵，然后將自動填充手柄拖到所需的單元格上。看截圖：

數字到單詞前往下載 30 天采購貝寶/ MyCommerce相關文章：如何在Excel中在歐洲和美國之間快速轉換日期格式？如何在Excel中將dD.MM.YYYY轉換為日期格式（mM / DD / YYYY）？如何在Excel中的日期和Unix時間戳之間轉換？