Sorting with empty cells

[Paul Kraemer]

Hi,
I am using Excel 2007. I have a worksheet one which one of my columns
contains mostly numerical data, but I have this column formatted as Text.
The data in this column is basically a series of integers, but for certain
integers I have a second row for which I have appended an asterisk (*) to the
end of the integer value. For example, my data might look like this:

1001
1002
1002*
1003

I would like to be able to sort my data as I've shown above, but I can't
figure out how to do this. Anytime I try to sort on this column (sorting by
values from smallest to largest), I get the following order:

1001
1002
1003
1002*

If anyone can tell me how I can sort and get the order that I want, I would
really appreciate it. Thanks in advance,

Paul

 

[Luke M]

Even though you formatted the cells as text, it appears the integers were
entered as numbers. Note that if you first format cells as text, then input
your example values, they will sort the way you are wanting.

to quickly convert your column to text, you could use a helper row with:
=TEXT(A1,"@")
and copy down. Then do a copy - paste special values to create a static list
of text numbers that you can now sort.

 

[JLatham]

And to add to what Luke said - in the future, precede your entries with a
single apostrophe to force them to text, as
'1234
or
'1234*
That'll keep you from having to repeat Luke's instructions in the future.

 

[Gary''s Student]

On the Home tab, pull down Sort & Filter and pick Sort A to Z

 

[Paul Kraemer]

Hi Luke,

Thanks for your response. Creating the helper column and pasting values
worked great. Just for my own understanding, what is the meaning of the "@"
in the formula =TEXT(A1, "@")?

In the online help, I don't see any mention of "@" even being an option for
the "format_text" parameter of the TEXT function. Am I just missing it?

Thanks again,
Paul

 

[Luke M]

The @ symbol is apparently XL speak for the "text" format. The TEXT function
uses the same symbols as the custom format when you format a cell. So, since
I knew I wanted the "text" format, I selected text formatting, then went to
the custom format. The formatting displayed is the last format you chose.

My guess? They thought the @ symbol was a good way to represent "alpha" aka
text.