Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting with empty cells
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 -- Paul Kraemer |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting with empty cells
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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Paul Kraemer" wrote: 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 -- Paul Kraemer |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting with empty cells
On the Home tab, pull down Sort & Filter and pick Sort A to Z
-- Gary''s Student - gsnu200856 "Paul Kraemer" wrote: 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 -- Paul Kraemer |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting with empty cells
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. "Paul Kraemer" wrote: 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 -- Paul Kraemer |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting with empty cells
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 -- Paul Kraemer "Luke M" wrote: 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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Paul Kraemer" wrote: 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 -- Paul Kraemer |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting with empty cells
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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Paul Kraemer" wrote: 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 -- Paul Kraemer "Luke M" wrote: 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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Paul Kraemer" wrote: 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 -- Paul Kraemer |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting and deleting empty cells | Excel Discussion (Misc queries) | |||
Sorting with formulaically "empty" cells | Excel Worksheet Functions | |||
Excel - Autom. Filter "Empty / Non Empty cells" should come first | Excel Discussion (Misc queries) | |||
When I SUM cells & 1 is empty I need the result to be empty not 0 | Excel Discussion (Misc queries) | |||
How can I convert empty strings to empty cells? | Excel Discussion (Misc queries) |