Incremental Text-Numbering

**NKRA** · 07-14-2010, 01:58 PM

All,

I want to write a formula which reviews a column A field which has (text numeric) items like below

Column A
ABC-10-123456
ABC-10-123456
CDA-10-324567
CAC-10-234789
CDA-10-324567

I want to assign a incremental value in Column B like this FN1 to FN nth number by reading column A

Column B
ABC-10-123456-FN1
ABC-10-123456-FN2
CDA-10-324567-FN1
CAC-10-234789-FN1
CDA-10-324567-FN2

preferably use formula then macro

Thnx
NKRA

**shg** · 07-14-2010, 03:16 PM

In B1 and copy down,

=A1 & "-FN" & COUNTIF(A$1:A1, A1)

**NKRA** · 07-14-2010, 03:33 PM

That formula worked but i noticed in some instance my data element has four text characters in Column A

Column A
ABC-10-123456
ABC-10-123456
CDA-10-324567
CAC-10-234789
CDA-10-324567
EFGH-10-123456
EFGH-10-123456
NKRA-10-000123
COOL-10-789654
COOL-10-789654

Basically where it had four text in begining it did not put FN1 or FN nth number just put like EFGH-10-123456-

Any further suggestion how to resolved.