0

I have this formula which helps me in creating auto numbering based on neighboring cells existence. however when there are nothing exists in neighboring cells then 0 get displayed.

I do not want this zero to be displayed. Could someone help me in modifying this below formula and providing me this would be great help. I want numbering as QR_01, QR_02 not with plain numbers 1,2,3..

=IF(C:C="",,"QR_"&TEXT(COUNTA(INDIRECT("C3:C"&ROW())),"0#"))

I don't want Macros please.

Thanks

Scott Craner
  • 137,650
  • 9
  • 45
  • 77
Jake
  • 65
  • 1
  • 13
  • `=IF(C:C="","","QR_"&TEXT(COUNTA(INDIRECT("C3:C"&ROW())),"0#"))` – JNevill Apr 24 '17 at 14:30
  • 1
    Do not make the mistake of converting numbers into text. Excel is all about numbers. If you convert them into text then you are loosing most of Excel's advantages and run into unnecessary problems such as this one: http://stackoverflow.com/questions/37274898/how-can-i-sort-a-a-table-with-vba-with-given-text-condition/37275560#37275560 If you want to format your numbers in a particular way then I urge you to use the `.NumberFormat` (for these particular cells) instead as shown in [this solution](http://stackoverflow.com/a/37275560/1153513). – Ralph Apr 24 '17 at 14:36

0 Answers0