I want to hide a single character within cells/formula bar, specifically the
opening bracket character: [
Reason: I have many tables, each with a column of names that need to be
alphabetized. Many of the names are in brackets [Like This], and need to stay
that way. In Word, I can globally hide the opening brackets, run my sort,
then un-hide the opening brackets. That's necessary to prevent a resulting
alphabetized list of bracketed names followed by an alphabetized list of
unbracketed names. I need them alphabetized, ignoring the brackets. I don't
want to convert the files to Word because there are good reasons I need them
in Excel (mostly automatic date formatting, which I don't think I can do in
Word).
I am *not* a power user of Excel. I can find info about hiding rows, columns
or worksheets, but nothing about hiding individual characters. Can I do that?
Or is there another way to accomplish what I am trying to do--basically
ignore the [ in sorting a column?

Not possible in Excel.



--


Regards,


Peo Sjoblom


"Anita Stuever" wrote in message
...
I want to hide a single character within cells/formula bar, specifically
the
opening bracket character: [
Reason: I have many tables, each with a column of names that need to be
alphabetized. Many of the names are in brackets [Like This], and need to
stay
that way. In Word, I can globally hide the opening brackets, run my sort,
then un-hide the opening brackets. That's necessary to prevent a resulting
alphabetized list of bracketed names followed by an alphabetized list of
unbracketed names. I need them alphabetized, ignoring the brackets. I
don't
want to convert the files to Word because there are good reasons I need
them
in Excel (mostly automatic date formatting, which I don't think I can do
in
Word).
I am *not* a power user of Excel. I can find info about hiding rows,
columns
or worksheets, but nothing about hiding individual characters. Can I do
that?
Or is there another way to accomplish what I am trying to do--basically
ignore the [ in sorting a column?

On Mon, 5 May 2008 20:06:01 -0700, Anita Stuever wrote:

I want to hide a single character within cells/formula bar, specifically the
opening bracket character: [
Reason: I have many tables, each with a column of names that need to be
alphabetized. Many of the names are in brackets [Like This], and need to stay
that way. In Word, I can globally hide the opening brackets, run my sort,
then un-hide the opening brackets. That's necessary to prevent a resulting
alphabetized list of bracketed names followed by an alphabetized list of
unbracketed names. I need them alphabetized, ignoring the brackets. I don't
want to convert the files to Word because there are good reasons I need them
in Excel (mostly automatic date formatting, which I don't think I can do in
Word).
I am *not* a power user of Excel. I can find info about hiding rows, columns
or worksheets, but nothing about hiding individual characters. Can I do that?
Or is there another way to accomplish what I am trying to do--basically
ignore the [ in sorting a column?

You can create additional column - used for sorting purposes only - and
fill it with something like this:

=SUBSTITUTE(A1,"[","")

Regards,
B.

Here's a slightly cumbersom way, but it works.
I'm assuming your names are in A1 to A10, and are in the form [name]

In B1, enter =RIGHT(A1,LEN(A1)-1) and copy down, which will give you a list
of names without the leading [

In C1, enter =("["&INDEX(B$1:B$10,MATCH(SMALL(COUNTIF(B$1:B$10,""& B$1:B$10),
ROWS(C1:C$10)),COUNTIF(B$1:B$10,""&B$1:B$10),0)))

as an array formula (enter with Cntrl-Shift-Enter) and drag down to C10.

This should sort and put the leading bracket back.

Dave

url:http://www.ureader.com/msg/103455487.aspx

In the first free column, enter this formula:
=IF(ISERROR(OR(SEARCH("[",A1),SEARCH("]",A1))),A1,MID(A1,SEARCH("[",A1)+1,SEARCH("]",A1)-2))

This example assumes your list of names begins in A1. Adjust that reference
accordingly. Copy the formula down through all the rows of data. Select all
the data, then sort by the new field.

Hope this helps,

Hutch

"Anita Stuever" wrote:

I want to hide a single character within cells/formula bar, specifically the
opening bracket character: [
Reason: I have many tables, each with a column of names that need to be
alphabetized. Many of the names are in brackets [Like This], and need to stay
that way. In Word, I can globally hide the opening brackets, run my sort,
then un-hide the opening brackets. That's necessary to prevent a resulting
alphabetized list of bracketed names followed by an alphabetized list of
unbracketed names. I need them alphabetized, ignoring the brackets. I don't
want to convert the files to Word because there are good reasons I need them
in Excel (mostly automatic date formatting, which I don't think I can do in
Word).
I am *not* a power user of Excel. I can find info about hiding rows, columns
or worksheets, but nothing about hiding individual characters. Can I do that?
Or is there another way to accomplish what I am trying to do--basically
ignore the [ in sorting a column?

Copy the column and paste to adjacent inserted column.

Do an editreplace for [ to replace with nothing.

Sort both columns on that column.

Delete the new column.


Gord Dibben MS Excel MVP

On Mon, 5 May 2008 20:06:01 -0700, Anita Stuever
wrote:

I want to hide a single character within cells/formula bar, specifically the
opening bracket character: [
Reason: I have many tables, each with a column of names that need to be
alphabetized. Many of the names are in brackets [Like This], and need to stay
that way. In Word, I can globally hide the opening brackets, run my sort,
then un-hide the opening brackets. That's necessary to prevent a resulting
alphabetized list of bracketed names followed by an alphabetized list of
unbracketed names. I need them alphabetized, ignoring the brackets. I don't
want to convert the files to Word because there are good reasons I need them
in Excel (mostly automatic date formatting, which I don't think I can do in
Word).
I am *not* a power user of Excel. I can find info about hiding rows, columns
or worksheets, but nothing about hiding individual characters. Can I do that?
Or is there another way to accomplish what I am trying to do--basically
ignore the [ in sorting a column?

Oh, Gord, that's brilliant! Other responses were great, too, but yours is
certainly the simplest--and it surely will work. Why didn't I think of that?

Thanks so much to all of you, particularly Gord!

Anita

Not so brilliant as lazy.

I don't like to deal with formulas in this type of operation if I can avoid it.


Gord

On Tue, 6 May 2008 12:10:02 -0700, Anita Stuever
wrote:

Oh, Gord, that's brilliant! Other responses were great, too, but yours is
certainly the simplest--and it surely will work. Why didn't I think of that?

Thanks so much to all of you, particularly Gord!

Anita