Home/ Questions/Q 6115
Next
In Process
Computing Staff
  • 1
Asked: 2021-11-18T06:53:19+00:00 In:

Autonumber Lines But Skip Blank Lines

  • 1

Hi,
how can I auto number lines in Excel 2010, only with text in the second column, see example:
1. abcd
blank
blank
2. efghi
3. jklmn

Share

    3 Answers

    1. If you need the period, then simply use Custom Formatting:
      On the Task Bar/Ribbon
      Home Tab
      Select Format
      Select Format Cells (At the bottom of the popup window)
      Select Number Tab
      Select Custom Format (Bottom of list)
      In the Type: box simply enter: 0.
      That is a zero followed by a period.
      Click Ok

      • 0

    2. Mike’s fine formula will indeed number your lines, but if you need the period, you might need VBA.
      Sub AutoNumText()
      lastRw = Range(“B” & Rows.Count).End(xlUp).Row
      For nxtRw = 1 To lastRw
      If Range(“B” & nxtRw) “” Then
      myNum = myNum + 1
      Range(“A” & nxtRw) = myNum & _
      Application.WorksheetFunction.Text(myNum, “\.”)
      End If
      Next
      End Sub

      • 0

    3. Not completely sure what you want, but try this:
      If your data look like this:

      A B
      1) 1 abcd
      2) 2 efgh
      3) 3 ijkl
      4)
      5) 4 mnop
      6) 5 qrst
      7)
      8) 6 uvwx

      First in cell A1, you must start the sequence by manually entering the number 1

      Next in cell A2 enter the formula:

      =IF(ISTEXT(B2),MAX($A$1:$A1)+1,””)

      Next drag the formula down as many cells as needed.

      See how that works.

      • 0