How to convert a cell with
Apple, Lemon, Water, Soda, Cap, Bat
1. Apple, 2. Lemon, 3. Water, 4. Cap, 5. Bat
Kutools for Excel is a powerful add-in that frees you from performing time-consuming operations in Excel, such as combine sheets quickly, merge cells without losing data, paste to only visible cells, count cells by color and so on. 300+ powerful features / functions for Excel 2019, 2016, 2013, 2010, 2007 or Office 365!
It enables tabbed browsing, editing, and managing of Microsoft Office applications. You can open multiple documents / files in a single tabbed window, such as using the browser IE 8/9/10, Firefox, and Google Chrome. It's compatible with Office 2019, 2016, 2013, 2010, 2007, 2003 or Office 365. Demo
Kutools for Outlook is a powerful add-in that frees you from time-consuming operations which majority of Outlook users has to perform daily! It can save your time from using Microsoft Outlook 2019, 2016, 2013, 2010 or Office 365!
Kutools for Word is a powerful add-in that frees you from time-consuming operations which majority of Word users have to perform daily! It can save your time from using Microsoft Word / Office 2019, 2016, 2013, 2010, 2007, 2003 or Office 365!
Restores the old look and menus of Office 2003 to Microsoft Office 2019, 2016, 2013, 2010, 2007 or Office 365. Don’t lose time in finding commands on the new Ribbon. Easy to deploy to all computers in enterprises and organizations.
How could we fill series numbers into a list of different size merged cells in Excel? First in our mind is to drag the Autofill handle to fill the merged cells, but, in this case, we will get the following warning message, and can’t fill the merged cells.
Since the fill handle does not support the merged cells, here, I can talk about some other tricks for solving this problem.
The following VBA code can help you to number the selected merged cells quickly. Please do as follows:
1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Auto number merged cells
Sub NumberCellsAndMergedCells() 'Update 20141028 Dim Rng As Range Dim WorkRng As Range On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type: = 8) Set WorkRng = WorkRng.Columns(1) xIndex = 1 Set Rng = WorkRng.Range("A1") Do While Not Intersect(Rng, WorkRng) Is Nothing Rng.Value = xIndex xIndex = xIndex + 1 Set Rng = Rng.MergeArea.Offset(1) Loop End Sub
3. Then press F5 key to run this code, and a prompt box will pop out to let you select the merged cells you want to fill, see screenshot:
4. After selecting the merged cells, and click OK, now, your selected merged cells have been filled with sequential numbers, see screenshot: