Sequential Numbers in Excel Value

[austinandrei]

Hi,
How do I add sequential numbering in excel?
Say I have below data

Cell A1 Mango
Cell A2 Apple
Cell A3 Orange
Cell A4 Banana

and I would like it to be like below which is a function in MS Word

Cell A1 1. Mango
Cell A2 2. Apple
Cell A3 3. Orange
Cell A4 4. Banana

I don't want to use concatenate. Just an excel trick if any.
Thanks!

 

[admiral100]

Hi,

Try this :

=LEFT(A1,FIND(ROW(A1),A1))&" "&SUBSTITUTE(A1,LEFT(A1,FIND(ROW(A1),A1)),ROW(A1)&".")

 

[Rick Rothstein]

You could put this formula...

=ROWS(A$1:A1)&". "&A1

in a blank cell and copy it down for as many rows as you have data in Column A, then copy those formula cells and PasteSpecial/Values over top of your original list, then delete the formulas.

 

[Rick Rothstein]

You could put this formula...

=ROWS(A$1:A1)&". "&A1

in a blank cell and copy it down for as many rows as you have data in Column A, then copy those formula cells and PasteSpecial/Values over top of your original list, then delete the formulas.

By the way, if you were thinking of using a macro, then you can consider this one...

[table="width: 500"]
[tr]
	[td]Sub InsertNumericalBullets()
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Value = Evaluate(Replace("IF(@="""","""",ROW(@)&"". ""&@)", "@", .Address))
  End With
End Sub[/td]
[/tr]
[/table]

 

[austinandrei]

Hi both, thanks for the reply. Yes I have just used the Rows formula to do the task. Was just thinking if there's a trick in excel that doesn't use macro or formula to do it just like in MS Word but it seems there's none. Thanks!

 

[Peter_SSs]

Was just thinking if there's a trick in excel that doesn't use macro or formula to do it..

You can do it without macro or formula, but it is more work than just using the formula.

- In another column, fill with the sequential numbers. This could be quick by entering the first 2, selecting them & double-clicking the Fill Handle if next to your actual data.
- In the next column type the first entry manually (eg "1. Mango")
- If you have Flash Fill enabled, as you begin to type the second entry (eg "2. ") the rest of the column of values should appear pale grey. Press Enter & they will be filled automatically.

Note too, that if a row of the data was subsequently deleted, this result would not auto-adjust like Word bullets or the formula method.