Formula to rank cells alphabetically

I have a list of names, e.g.

Bloggs, J
Smith, P
Peters, F

I want a formula that can rank these in order so the output would be

Bloggs, J 1
Smith, P 3
Peters, F 2

I know the sort function could be used but I need a formula.

I know the Rank function will do this for numbers but does not work on alpha
fields.
Can anyone help?

Thanks in advance

=COUNTIF($A$1:$A$5,"<"&A1)+1

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dave Shaw" <[email protected]> wrote in message
news:[email protected]...
> I have a list of names, e.g.
>
> Bloggs, J
> Smith, P
> Peters, F
>
> I want a formula that can rank these in order so the output would be
>
> Bloggs, J 1
> Smith, P 3
> Peters, F 2
>
> I know the sort function could be used but I need a formula.
>
> I know the Rank function will do this for numbers but does not work on
alpha
> fields.
> Can anyone help?
>
> Thanks in advance

Fantastic - so simple. I had started making a complicated formula based upon
code, left, right, etc.

Many thanks

Dave

"Bob Phillips" wrote:

> =COUNTIF($A$1:$A$5,"<"&A1)+1
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Dave Shaw" <[email protected]> wrote in message
> news:[email protected]...
> > I have a list of names, e.g.
> >
> > Bloggs, J
> > Smith, P
> > Peters, F
> >
> > I want a formula that can rank these in order so the output would be
> >
> > Bloggs, J 1
> > Smith, P 3
> > Peters, F 2
> >
> > I know the sort function could be used but I need a formula.
> >
> > I know the Rank function will do this for numbers but does not work on
> alpha
> > fields.
> > Can anyone help?
> >
> > Thanks in advance
>
>
>