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

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 uvwxFirst 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.

MIKE

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

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

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 theType:box simply enter: 0.

That is a zero followed by a period.

Click OkMIKE

Ask Your Question

Weekly Poll

How long do you think until flying cars or taxis are common?

Discuss in The Lounge

Poll History