How do i Autonumber the first column by avoiding the blank space and numbered cells - AM NEW TO BXCEL. Thanks

[Titus sabu]

SNO	NAME	CONTACT
1	PUBLIC SCHOOL 1	9855666
	930000
2	PUB SCHOOL 2	82899633

<tbody>
</tbody>

 

[vbanoob0807]

Hi Titus,

assuming your first col starts from Col A, and what i understand your numbered cells to avoid are like "930000", and you want to number off from Row 2 onwards, try input the formula below in cell A2 and drag down.

=IF(AND(NOT(ISBLANK(B2)),NOT(ISNUMBER(B2))),ROWS($B$2:$B2)-COUNTBLANK($B$2:$B2)-COUNT($B$2:$B2),"")

 

[njimack]

=if(istext(b2),max(a$1:a1)+1,"")

 

[vbanoob0807]

=if(istext(b2),max(a$1:a1)+1,"")

Fabulous

 

[Titus sabu]

Hi Titus,

assuming your first col starts from Col A, and what i understand your numbered cells to avoid are like "930000", and you want to number off from Row 2 onwards, try input the formula below in cell A2 and drag down.

=IF(AND(NOT(ISBLANK(B2)),NOT(ISNUMBER(B2))),ROWS($B$2:$B2)-COUNTBLANK($B$2:$B2)-COUNT($B$2:$B2),"")

vbanoob0807, Thank you So Much. Exactly what i needed.

 

[Titus sabu]

Thank you Neil, so much for This reply. It did what exactly i need. Since I am new to excel this really blew me off since it had taken hours manually.

 

[Titus sabu]

Thank you !!