跳到主要內容

如何在Excel中將1-12轉換為月份名稱?

假設您剛剛收到一份銷售報告,其中月份被列為從 1 到 12 的數字,您需要將它們顯示為相應的月份名稱,如下面的螢幕截圖所示。想知道如何實現這個目標?在本文中,我們將指導您透過兩種有效的方法將月份數字無縫轉換為月份名稱。


使用 TEXT 函數將月份數字轉換為月份名稱

實際上,我們可以應用 TEXT 函數可在 Excel 中輕鬆將月份數字(從 1 到 12)轉換為月份名稱。請依照以下步驟操作:

  1. 選取要顯示月份名稱的空白儲存格(例如範例中的儲存格 C2),然後鍵入下列公式:
    =TEXT(A2*29,"mmm")
    小提示: A2 是月份清單中的第一個儲存格,您將開始將月份數字轉換為月份名稱。
  2. 向下拖曳公式單元格的填充柄以將公式套用到下面的儲存格。
注意: 要將數字轉換為完整的月份名稱(例如“January”、“February”)而不是縮寫,您需要使用不同的公式:
=TEXT(A2*29,"mmmm")

使用 Kutools for Excel 將月份數字轉換為月份名稱

在上面的例子中,很明顯 TEXT 函數擅長建立月份名稱,但它是在一個新的單獨儲存格中執行的。 對於那些有 Excel的Kutools 他們可以使用一種更直接的方法。 透過使用 手術 Kutools 中的功能,您可以直接用對應的月份名稱取代月份數字(範圍從 1 到 12)。

Excel的Kutools - 包含 300 多個 Excel 基本工具。 享受全功能 30 天免費試用,無需信用卡! 現在下載!

  1. 選擇要轉換為對應月份名稱的數字。
  2. 庫工具 標籤,在 編輯 組,選擇 更多 > 手術.
    小提示: 根據您的顯示設置, 手術 命令也可能在 編輯 組。
  3. 操作工具 對話框,請執行以下操作:
    1. 點擊 習俗 在選項 手術 框。
    2. 輸入公式 =TEXT(?*29,"mmm") ,在 習俗 框。 小提示: 更換 “嗯” with “嗯嗯” 如果您想顯示月份的全名而不是三個字母的縮寫,請在公式中新增。
    3. (可選)檢查 創建公式 選項。 小提示: 如果您希望公式出現在儲存格中,請選取此選項。如果您不選擇此選項,則儲存格將僅顯示公式的最終結果。
    4. 點擊 Ok.

結果

現在您將觀察到月份名稱已直接取代了所選數字。 看截圖:

注意: 想要訪問 手術 公用事業? 下載 Excel的Kutools 現在! 除此之外,Kutools 還擁有 300 多個其他功能,並提供 30 天免費試用。 不要等待,今天就來試試吧!


影片:如何在 Excel 中將 1-12 轉換為月份名稱?


相關文章:

如何在Excel中將日期轉換為工作日名稱或月份名稱?

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (16)
Rated 4.5 out of 5 · 2 ratings
This comment was minimized by the moderator on the site
I get the same issue, the only return is "00" regardless of using *28 or *29 in the formula. A column only includes numbers 1-12 so and are in text format, so I can't figure out the issue.
This comment was minimized by the moderator on the site
Hi there,

Can you show me a screenshot of your data?
Note: Please select the cell where you entered the formula and then take a screenshot that includes both your data and the formula bar.

Amanda
This comment was minimized by the moderator on the site
For some reason neither the TEXT formula (=TEXT(cell reference;"mmmm")) nor setting the custom cell format to mmmm is working - both simply return the value "00". Any ideas why this is happening? Simply using the MONTH formula returns the correct month number, so the problem can't be the format of the date.
This comment was minimized by the moderator on the site
Hi there,

I think the formula in the tutorial is =TEXT(A2*29,"mmm"). And A2 in the formula is the number, say, 2, that you want to convert it to the month text, say, February.

Amanda
This comment was minimized by the moderator on the site
is there a way to do the kutools example above without getting the extension? For example I type number 1 - 12 on a cell and it automatically changes to a month name?
This comment was minimized by the moderator on the site
Can you explain why is number 29 in this formula ?
Rated 4.5 out of 5
This comment was minimized by the moderator on the site
You could also use 28 instead of 29 with the same results.
30 will fail for February and 27 will fail for December, so the only options are 28 and 29
John #30837
30 should fail for February but doesn't because Excel incorrectly assumes that 1900 was a leap year.
27 actually fails for September, October, November and December
Chris #30838

Hi, the above two comments from the page should explain the reason.
If you have other questions, please don't hesitate to ask. :)

Amanda
This comment was minimized by the moderator on the site
Principle works. 29 produces error if you have more than few years. 30.4167 works for more than 10 years.
This comment was minimized by the moderator on the site
Coba ini..


=TEXT( "1/" & A2 & "/2022 " ; "m mm mmm mmmm" )


Keterangan :
"1/" dibuat per tanggal 1
A2 cell angka bulan 1 - 12
"/2022" dibuat per tahun 2022
"m mm mmm mmmm" untuk memilih format.

Nanti hasilnya 4 04 Apr April
Rated 4.5 out of 5
This comment was minimized by the moderator on the site
Hi, are you tring to have a list of dates in format: day/month/year?
If so, you can just use a formula like this: ="1/"&A2&"/2022".

Amanda
This comment was minimized by the moderator on the site
Gracias, me sirvio mucho tu explicación.
This comment was minimized by the moderator on the site
Very helpful! Thanks so much!
This comment was minimized by the moderator on the site
Can you explain why is number 29 in this formula ?
This comment was minimized by the moderator on the site
The text formula treats the number as a day in the year. So 1 would be 1st of January, and 365 would be 31st December. If you convert these numbers to months then 1 would be January and 365 would be December. With 2, the formula would see this as 2nd of January so would still return as January. However, with the 29, we can make this 2 58 instead, which alludes to Feb 27th. The formula will thus return this as feb. The same is applied for the numbers 1 to 12. Hope this helps
This comment was minimized by the moderator on the site
You could also use 28 instead of 29 with the same results.

30 will fail for February and 27 will fail for December, so the only options are 28 and 29
This comment was minimized by the moderator on the site
30 should fail for February but doesn't because Excel incorrectly assumes that 1900 was a leap year.
27 actually fails for September, October, November and December
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations