跳到主要內容

如何在Excel中自動增加一個字母以獲得下一個字母？

假設您有一個包含字母AB的單元格，則需要將最後一個字母增加一個，以獲取下一個字母AC，AD等。 或者，您有一個字母A，並且想要將該字母增加到B，C…以創建一個字母列表。 如何實現呢？ 本文可以為您提供詳細信息。

用公式將字母AB加XNUMX以得到AC，AD…
將字母加XNUMX可獲得帶有公式的字母列表
使用用戶定義的功能將字母增加XNUMX以獲取字母列表

用公式將字母AB加XNUMX以得到AC，AD…

請按照以下步驟在Excel中將字母增加XNUMX。

1.在給定字母下方選擇一個空白單元格，在其中輸入以下公式，然後按 Enter 鍵。 

=IF(RIGHT($A2,1)="Z", CHAR(CODE(LEFT(A2,1))+1),LEFT(A2,1))&CHAR(65+MOD(CODE(RIGHT(A2,1))+1-65,26))

備註：在公式中，A2是包含給定字母的單元格。

2.繼續選擇A3，向下拖動“填充手柄”以獲取所需字母的列表。

將字母A加XNUMX以得到帶有公式的字母列表

您可以使用 =字符（CODE（A1）+1） 在Excel中將字母加XNUMX的公式。 請執行以下操作。

1.在給定字母下方選擇一個空白單元格，輸入公式 =字符（CODE（A2）+1） 進入編輯欄，然後按 Enter 鍵。 看截圖：

2.選擇結果單元格，向下拖動“填充手柄”以獲取所需的字母。

備註：使用此公式，得到字母Z後，如果繼續向下拖動Fill Handle，將得到一些特殊字符，如下圖所示。 如果要在Z後面獲得字母AA，可以使用以下用戶定義的函數來幫助您。

使用用戶定義的功能將字母增加XNUMX以獲取字母列表

以下用戶定義的函數還可以幫助您在Excel中增加一個字母。

1。 按 其他 + F11 同時打開 Microsoft Visual Basic for Applications 窗口。

2。 在裡面 Microsoft Visual Basic for Applications 窗口中，單擊 插入 > 模塊。 然後將以下VBA代碼複製並粘貼到 模塊 窗口。

VBA代碼：將字母增加一個以獲取字母列表

Function ColLtrs(i As Long) As String
    ColLtrs = Replace(Cells(1, i).Address(False, False), "1", "")
End Function

3。 按 其他 + Q 關閉鍵 Microsoft Visual Basic for Applications 窗口。

4.在給定字母下方選擇一個空白單元格，輸入公式 = ColLtrs（ROW（）），然後按 Enter 獲得第二個字母的鑰匙。

2.選擇結果單元格，向下拖動“填充手柄”以獲取所需的字母。

備註：使用此方法，您增加的字母基於行號。 您需要在工作表的第一行中開始第一個字母A，否則，該字母將變得混亂。

最佳辦公生產力工具

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

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

產品描述

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

  • 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%，每天為您減少數百次鼠標點擊！
Comments (21)
No ratings yet. Be the first to rate!
rajan kumar
rajan kumar
This comment was minimized by the moderator on the site
is there any formula to increase or write ascending order like 1nc1, 1nc2, 1nc3.....
Reply
0
0
crystal
crystal     rajan kumar
This comment was minimized by the moderator on the site
Hi,

To create an incrementing sequence like "1nc1", "1nc2", "1nc3", etc., in Excel, you can use a formula that leverages the row number to generate the numeric part of the sequence.

Assuming you want to start this sequence in cell A1, you would enter the following formula in A1:
="1nc" & ROW(A1)
Then, drag this formula down to the desired number of rows. Each cell will generate a part of the sequence based on its row number. For example, cell A2 will display “1nc2”, A3 will display “1nc3”, and so on.

The logic behind this formula is:
ROW(A1) returns the row number of cell A1, which is 1.
"1nc" & ROW(A1) concatenates the string “1nc” with the row number, creating “1nc1”.
Reply
Report
0
0
Mayra Lopez
Mayra Lopez
This comment was minimized by the moderator on the site
I need a code to help make my rows from 1-A to 1-Z please
Reply
0
0
crystal
crystal     Mayra Lopez
This comment was minimized by the moderator on the site
Hi Mayra Lopez,
The VBA code provided in the post is still helpful, but you need to apply the following formula to get the results you need:
="1-"&ColLtrs(ROW())
Reply
Report
0
0
Willie B
Willie B
This comment was minimized by the moderator on the site
How would I create a formula that advances like this?:

AAAAAAAAA
AAAAAAAAB
AAAAAAAAC
AAAAAAAAD
etc.
Reply
0
0
crystal
crystal     Willie B
This comment was minimized by the moderator on the site
Hi Willie B,
You can try the VBA code provided in the post. After adding the code, use this formula to get the result you need:
="AAAAAAAA"&ColLtrs(ROW())
Reply
Report
0
0
Rachel Hita
Rachel Hita
This comment was minimized by the moderator on the site
I need a code for increasing my row cell values from AA-01-A01, AA-01-A02 to AA-01-B01, AA-01-B02. And I also need a code for my colomn cell values from AA-01-A01, AA-01-A02 to AA-03-A01, A-03-A02. Help.
Reply
0
0
KarenS
KarenS
This comment was minimized by the moderator on the site
I need a code from A-Z, AA-ZZ, AAA to ZZZ, AAAA to ZZZZ
Reply
0
0
crystal
crystal     KarenS
This comment was minimized by the moderator on the site
Hi,Sorry can't help with this problem. You can post the problem to the forum below to get help from other Excel enthusiasts.
https://www.extendoffice.com/forum/kutools-for-excel.html 
Reply
Report
0
0
brickh7777@gmail.com
This comment was minimized by the moderator on the site
How do I get it to start over at A when Z is used?
Reply
Report
0
0
Zac
Zac
This comment was minimized by the moderator on the site
This code will allow you to go from A to ZZ.

=IF(B2="Z","AA",IF(LEN(B2)=1,CHAR(CODE(B2)+1),IF(RIGHT(B2,1)="Z",CHAR(CODE(LEFT(B2,1))+1),LEFT(B2,1))&CHAR(65+MOD(CODE(RIGHT(B2,1))+1-65,26))))
Reply
0
0
Gazza
Gazza     Zac
This comment was minimized by the moderator on the site
Thank you Zac for the letter sequence formula, saved me a ton load of work
Reply
0
0
Dan
Dan
This comment was minimized by the moderator on the site
Is there a way to do this for entire words? Where every letter in the word moves up by X number of characters
Reply
0
0
Harold McBroom
Harold McBroom
This comment was minimized by the moderator on the site
Excel was the spreadsheet that superseded Lotus 1-2-3, and having been around that long, you would think that geniuses at Microsoft would have created a more simple means of retrieving a cells column number, without having to write your own function. Back in 2001, 2002 when I was programming in VBA there were only 65536 rows in an Excel spreadsheet, now the number has increased to a ridiculous 1.4 million rows. It's not how big the database is that counts, but how intelligent you manage the data on your hard drive, when accessing that data through Excel.

If that doesn't make you upset, then the Bankers Rounding scheme used by Excel should finish the job! We were taught math in school, basic rounding, come to find out, the Bankers "guild" wants to determine how cash amounts are rounded to better cater to their advantages. For instance, 1.5 is either closer to 1 or 2, but is rounded up to 2, being the even number. 0.5 is either closer to 0 or 1, but being the odd number, they round it down to 0. The entire system is corrupt, and they create the business software used by American businesses.



I read a comment below the one I'm addressing, and someone basically said, "I have a lot of banker friends, and they never heard of Bankers Rounding; They say they used a method called "5 Step Rounding" in the EU. So does that mean the Bankers Rounding is used only in the sabotage of the American Economy, but not good enough to ruin the European Economy that's trying to take over the world?! These money changers never cease to amaze me at how far they will go in their arrogance!
Reply
0
0
Me
Me     Harold McBroom
This comment was minimized by the moderator on the site
Everything man does is corrupt. Everything.
Reply
0
0
afzalss
afzalss
This comment was minimized by the moderator on the site
Dear Sir
When I run the function =ColLtrs(ROW()) in an excel sheet, I get perfect results, but if I want to call this function or cell value in VBA, then is it possible. So my variable could be i with values 1,2,3,4,....27,28, etc and result should be A,B,C,D....,AA,AB and so on. Is it possible?
Thanks
Reply
Report
0
0
crystal
crystal     afzalss
This comment was minimized by the moderator on the site
Hi,
Please select the number cells and run the below code to solve the problem.

Sub NumTOAZ()
Dim xRg As Range
Dim xStr As String
On Error Resume Next
For Each xRg In Selection
xStr = Replace(Cells(1, xRg.Value).Address(False, False), "1", "")
xRg.Value = xStr
Next
End Sub
Reply
Report
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations