autonumber lines but skip blank lines

May 22, 2011 at 05:01:22
Specs: Windows 7
how can I auto number lines in Excel 2010, only with text in the second column, see example:
1. abcd
2. efghi
3. jklmn

See More: autonumber lines but skip blank lines

Report •

May 22, 2011 at 10:32:44
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
5)  4   mnop
6)  5   qrst
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:


Next drag the formula down as many cells as needed.

See how that works.


Report •

May 22, 2011 at 14:11:25
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
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

May 22, 2011 at 16:35:40
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


Report •

Related Solutions

Ask Question