How to ignore parens in a sort?

[baroen]

I'm trying to figure out how to ignore a parentheses, i.e. (, in an
alphabetical list so the the data is sorted using the first letter
inside the paren. For example; ant, (ape), bear, (bat), etc. is the
correct order but the Excel default sorts the names with parens as
numbers and returns (ape), (bat), ant, bear.
Thanks

 

[bpeltzer]

You could create a 'helper' column that has the text sans parens, sort the
two columns together based just on the order of the helper column, then
delete the helper. If the list begins in A2, then in B2 you'd enter the
formula =SUBSTITUTE(SUBSTITUTE(A2,"(",""),")",""). Autofill that through
column B, then sort based on column B.

 

[Jim Cone]

b,

My Excel add-in Special Sort will sort on the data occurring
after a specific character. (suffix sort)

It has over 20 different sort methods not readily available in Excel.
They include sorting by... numbers only,
color, prefix, middle, suffix, random, reverse,
no articles, dates, dewey decimal, length and others.

Works and looks somewhat like the Excel sort utility.
Comes with a Word.doc install/use file.

It is - free - just email me and ask for it.
Remove XXX from my email address.

Jim Cone
San Francisco, USA
(e-mail address removed)


...
I'm trying to figure out how to ignore a parentheses, i.e. (, in an
alphabetical list so the the data is sorted using the first letter
inside the paren. For example; ant, (ape), bear, (bat), etc. is the
correct order but the Excel default sorts the names with parens as
numbers and returns (ape), (bat), ant, bear.
Thanks

 

[David McRitchie]

Make a helper column
C1: =TRIM(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""))

the Trim isn't really necessary,
Make a helper column in this case C based on Column A
and sort on Column C instead of 1 as your major sort field,
you probably want to use Ctrl+A to select all cells (ctrl+shift+spacebar on Excel 2003).