hiding individual characters in Excel

[Anita Stuever]

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?

 

[Peo Sjoblom]

Not possible in Excel.



--


Regards,


Peo Sjoblom

 

[Boris]

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.

 

[Tom Hutchins]

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

 

[Gord Dibben]

Copy the column and paste to adjacent inserted column.

Do an edit>replace for [ to replace with nothing.

Sort both columns on that column.

Delete the new column.


Gord Dibben MS Excel MVP

 

[Anita Stuever]

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

 

[Gord Dibben]

Not so brilliant as lazy.

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


Gord