How to Rank Alphabets with Numbers?

[dluhut]

Hello

I have the followings

BRNS1-1
BRNS1-2
BRNS1-3
...
...
BRNS5-1
BRNS5-2
BRNS5-3
...
BRNS100-1
BRNS100-2
BRNS100-3

In the original table, these BRNS numbers are not in order. Thus, I would like to know how can I rank them from the first digit (in the example would be 1, 5 and 100) follow by the digit after (in the example would be 1, 2 and 3).

So BRNS1-1 would be rank 1st, follow by BRNS1-2 and last would be BRNS100-3

I tried to use

Please Login or Register  to view this content.

but it's not giving me the correct "ranking"

[Ace_XL]

Are values after the "-" upto 3 only? In that case, use a helper column.

In helper column use
=SUBSTITUTE(SUBSTITUTE(A1,"BRNS",""),"-","")*1

Rank this column

[dluhut]

No...the values are actually starting from BRNS.

So BRNS1-1 would be rank 1st, follow by BRNS1-2 and last would be BRNS100-3

[FDibbins]

Try this, using a helper column (for instance, column B), copied down...
=IFERROR(--SUBSTITUTE(MID(A1,5,99),"-","."),"")

Then rank using this...
=RANK(B1,$B$1:$B$12,1)

[samba_ravi]

Post sample file

[kvsrinivasamurthy]

Pl see attached file with formula.