Sorting with dashes/hyphens/-'s

S

Sean E.

I have a problem sorting in excell, and I can't find a solution for this.
Excel seems to ingore dashes/hyphens.

E.g.,
If I sort the following in ascending order:
Saint-Claude
Saint-Elie
Sainte-Agathe
Saint-Leon

I get the following:
Saint-Claude
Sainte-Agathe
Saint-Elie
Saint-Leon

If I sort it descening, I get the following:
Saint-Leon
Saint-Elie
Sainte-Agathe
Saint-Claude

Ideally, I would like them to sort like this (in ascending order):
Saint-Claude
Saint-Elie
Saint-Leon
Sainte-Agathe

Any suggestions?

Sean
 
J

Jason Morin

Excel sorts in a certain order with regard to numerical
and non-numerical characters. The Help menu can provide
more clarity - search for "default sort orders". As for
your dilemma, you could use a helper column that
incorporates the formula:

=SUBSTITUTE(A1,"-",1)

Now select both columns and sort ascending on this new
column.

HTH
Jason
Atlanta, GA
 
S

Sean E.

Jason Morin said:
your dilemma, you could use a helper column that
incorporates the formula:

=SUBSTITUTE(A1,"-",1)

Now select both columns and sort ascending on this new
column.
Click to expand...

Thanks, I'll give that a try.

Sean
 
Top