How to order a column ignoring the hyphens

[bayliss_trevor]

Hi all,
I have a column that orders fine a-z except for the fact that it doesn't group words together with hyphens e.g.:
1.my-flies
2.myflies
3.my-fly
4.myfly

when I want it to be
1.my-flies
2.my-fly
3.myfly
4.myflies

how can I do that please? Thanks and happy xmas

[oldchippy]

Does this work for you? Use a helper column, then sort by column B, then A

=IF(ISERROR(IF(SEARCH("-",A1),0,1)),1,0)

[shg]

You can use a helper column with the formula =substitute(A1, "-", "") and sort by that.

[oldchippy]

shg - Nice one!

[Ron Coderre]

Excel sorting ignores apostrophes (') and hyphens (-).
http://support.microsoft.com/kb/322067

You could try replacing all hyphens with another character...perhaps a pipe ( | )....sort....then replace the pipes with hyphens.

Does that help?

[shg]

WTY, OC. Merry Christmas.

[bayliss_trevor]

Thank you for the replies really appreciated. What I have done is a combination of shg and ron's posts. I made another column and put =SUBSTITUTE(A1, "-", "|") getting rid of the hyphens and then sorted which is fine. What I need to do know now is how to replace the pipes with hyphens. Would I do this on another column? It seems a bit unwieldy to have my initial column then another one substituting the hyphens with pipes and then another one substituting the pipes with hyphens-am I missing something here should there only be one column? Thank you

[Ron Coderre]

Since you're using formulas....After you sort on the formula column,
won't you still have the original column?

[shg]

???

If you have a column with hyphens, and you have another column where the hyphen are replaced with pipes, why would you need yet another column to replace the pipes with hyphens -- that's what's in the original column.

[bayliss_trevor]

Thanks yes you are right sorry it all got a bit confusing. It is now sorted fine, if i sort on the new column the original column now orders fine. I have to keep in that helper column to be able to sort it correctly I understand now, cheers guys!

[shg]

If you're a happy camper, would you please mark the thread as Solved.

Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the words No prefix.
Change to Solved
Click Save