Re: Completely baffled on what should be simple

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

You're sorting the values--not the format.

And the custom format is used to make it look pretty--not to change the number
value back to text.

If you really want the values re-converted to Text (why???), you could use a
helper column filled with formulas like:

=text(a1,"0000")
(and drag down.)

I think I'd convert that column to real numbers, then format the whole column
using a custom format of 0000 and walk away happy.

Is there some reason you have to have the values converted back to text?

Pat Hughes wrote:

Ok, sorry for the confusion. I think I have it. I had to do the

Selecting an empty cell
edit|Copy
select the range to fix
edit|Paste special|check Add

to the column. THen do the sort. Then do the custom 0000 format.

I thought by just changing the format would do the trick but it doesn't. So
is it everytime you change the format you have to do the
"Selecting an empty cell
edit|Copy
select the range to fix
edit|Paste special|check Add"
in order to change the value to numbers? Do I have to do something similar
to make sure when I change cells from number to text to make sure the value
is correct. I guess I am confused about this. I've used excel a lot for
about 15 years but I never had this problem. Thanks so much for your
patience.
I do still wonder why if I have the cells formated as text that the sort
wouldn't work because of what I read in the Microsoft Excel Help

"Alphanumeric sort
When you sort alphanumeric text, Excel sorts left to
right, character by character. For example, if a cell contains the text
"A100," Excel places the cell after a cell that contains the entry "A1" and
before a cell that contains the entry "A11."

Text and text that includes numbers are sorted in the following order:

0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { |
} ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z "

This would make me think that it should work being in text with 0 being
first.
Thanks for all your help.
Pat

--

Dave Peterson
.

Relevant Pages

  • Re: Cant Change and Re-Sort
    ... But I'd double check to make sure the value in the cell is a real date. ... > The dates in the Complete and Referred Columns are Date Format. ... > then try to resort, the changed line appears at the bottom, and out of sort. ... > was imported into Excel. ...
    (microsoft.public.excel.misc)
  • Re: OT OT Excel ??????
    ... but when I go to the next cell, the number reverts to just 67. ... Data Sort properly. ... As the guy said you simply type a single apostrophe before the number ... No need to bother with format ...
    (uk.rec.gardening)
  • RE: Sort specific columns
    ... First thing is you specify the column to sort using the sorting list. ... Microsoft Excel uses the following order. ... if a cell contains the text ... To format a number as text, click Cells on the Format menu, click the ...
    (microsoft.public.excel.misc)
  • Re: Completely baffled on what should be simple
    ... the same thing I previously did, changing format to custom ... sort, it didn't work. ... if a cell contains the text ...
    (microsoft.public.excel.misc)
  • Re: Completely baffled on what should be simple
    ... And remember just changing the format of the cell won't change the underlying ... sort, it didn't work. ... for the extensions that have either one or two 0's as the first numbers, ...
    (microsoft.public.excel.misc)