Got it, let's tackle this efficiently—you're dealing with thousands of cells, so manual dragging is definitely not the way to go. Here are two reliable methods for Excel that'll automate filling those card pack titles into Column A:

This is great if you want a quick, no-code solution that works in real-time:

• First, in the cell next to your first card pack title (e.g., A2, matching B2's title), enter =B2 to set the initial title.
• In the cell below that (e.g., A3), paste this formula:

  =IF(ISNUMBER(SEARCH("卡包", B3)), B3, A2)
  

  Note: Adjust the "卡包" part if your titles use a different keyword (like "Pack" if they're in English). If titles are formatted differently (e.g., bolded), use =IF(CELL("fontbold", B3), B3, A2) instead—just press F9 to refresh if needed.
• Hover over the bottom-right corner of A3 until you see the fill handle (a small square), then double-click it. Excel will automatically fill the formula down to every row with content in Column B—no dragging required!

If you're working with tens of thousands of rows, a VBA script will run faster and avoid any formula lag:

1. Open your Excel file and press Alt + F11 to launch the VBA Editor.
2. Right-click your workbook name in the left pane, select Insert → Module.
3. Paste this code into the module window:

   Sub FillCardPackTitles()
       Dim ws As Worksheet
       Dim lastRow As Long
       Dim currentTitle As String
       Dim i As Long
       
       ' Update "Sheet1" to your actual worksheet name
       Set ws = ThisWorkbook.Sheets("Sheet1")
       lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
       
       ' Set the first title
       currentTitle = ws.Range("B2").Value
       ws.Range("A2").Value = currentTitle
       
       ' Loop through all rows
       For i = 3 To lastRow
           ' Check if current B cell is a new card pack title (adjust the keyword as needed)
           If InStr(1, ws.Range("B" & i).Value, "卡包", vbTextCompare) > 0 Then
               currentTitle = ws.Range("B" & i).Value
           End If
           ws.Range("A" & i).Value = currentTitle
       Next i
       
       MsgBox "Card pack titles filled successfully!", vbInformation
   End Sub
   

4. Tweak the code to match your setup:
   • Change "Sheet1" to your worksheet's name.
   • Modify the "卡包" keyword if your titles use different language or terminology.
   • If titles are bolded, replace the If InStr... line with If ws.Range("B" & i).Font.Bold = True Then.
5. Press F5 to run the script, or go back to Excel, click Developer → Macros, select FillCardPackTitles, and hit Run.

Both methods eliminate manual work entirely—pick the one that fits your dataset size and comfort level!

内容的提问来源于stack exchange，提问作者mileyk