software

i am trying to sort a non sequential list of numbers mixed with numbers
marked with an asterisk in front, such as on a bank statement of checks:

1224
1222
*1223
1221
1227
*1225
1228

and so on. When I sort, all of the numbers with an asterisk are grouped
together. I formatted them as text with the same result. Is there anything
i can do?

I would use a helper column to CONCATENATE a .1 on the end of all the numbers
with a leading asterisk, and then use Edit gt; Replace to get rid of the
asterisks, then all should sort normally.........you can reverse the
procedure at the end if you wish.

Vaya con Dios,
Chuck, CABGx3
quot;jstephensonquot; wrote:

gt; i am trying to sort a non sequential list of numbers mixed with numbers
gt; marked with an asterisk in front, such as on a bank statement of checks:
gt;
gt; 1224
gt; 1222
gt; *1223
gt; 1221
gt; 1227
gt; *1225
gt; 1228
gt;
gt; and so on. When I sort, all of the numbers with an asterisk are grouped
gt; together. I formatted them as text with the same result. Is there anything
gt; i can do?

If you don't need the asterisks, then just do a Find/Replace.

Find: ~*
Leave the Replace With Field blank

If you need to keep the asterisks, then I'd suggest adding an additional
column and using the following formula:

=SUBSTITUTE(A1,quot;*quot;,quot;quot;)

Then sort by this new column.

HTH,
Elkar

quot;jstephensonquot; wrote:

gt; i am trying to sort a non sequential list of numbers mixed with numbers
gt; marked with an asterisk in front, such as on a bank statement of checks:
gt;
gt; 1224
gt; 1222
gt; *1223
gt; 1221
gt; 1227
gt; *1225
gt; 1228
gt;
gt; and so on. When I sort, all of the numbers with an asterisk are grouped
gt; together. I formatted them as text with the same result. Is there anything
gt; i can do?

On Fri, 10 Feb 2006 11:57:20 -0800, quot;jstephensonquot;
gt; wrote:

gt;i am trying to sort a non sequential list of numbers mixed with numbers
gt;marked with an asterisk in front, such as on a bank statement of checks:
gt;
gt;1224
gt;1222
gt;*1223
gt;1221
gt;1227
gt;*1225
gt;1228
gt;
gt;and so on. When I sort, all of the numbers with an asterisk are grouped
gt;together. I formatted them as text with the same result. Is there anything
gt;i can do?

Simplest method: use a helper column for sorting.

If your data is in A1:An, then

B1:=IF(ISERR(-A1),--MID(A1,2,10),--A1)
copy/drag down to Bn.

Then select both columns (and any others of importance)
Data/Sort
Ascending
Col B

If you have a header row, sort on the helper column's header.--ron

thanks, that did it

quot;Elkarquot; wrote:

gt; If you don't need the asterisks, then just do a Find/Replace.
gt;
gt; Find: ~*
gt; Leave the Replace With Field blank
gt;
gt; If you need to keep the asterisks, then I'd suggest adding an additional
gt; column and using the following formula:
gt;
gt; =SUBSTITUTE(A1,quot;*quot;,quot;quot;)
gt;
gt; Then sort by this new column.
gt;
gt; HTH,
gt; Elkar
gt;
gt; quot;jstephensonquot; wrote:
gt;
gt; gt; i am trying to sort a non sequential list of numbers mixed with numbers
gt; gt; marked with an asterisk in front, such as on a bank statement of checks:
gt; gt;
gt; gt; 1224
gt; gt; 1222
gt; gt; *1223
gt; gt; 1221
gt; gt; 1227
gt; gt; *1225
gt; gt; 1228
gt; gt;
gt; gt; and so on. When I sort, all of the numbers with an asterisk are grouped
gt; gt; together. I formatted them as text with the same result. Is there anything
gt; gt; i can do?

thanks, that did it

quot;CLRquot; wrote:

gt; I would use a helper column to CONCATENATE a .1 on the end of all the numbers
gt; with a leading asterisk, and then use Edit gt; Replace to get rid of the
gt; asterisks, then all should sort normally.........you can reverse the
gt; procedure at the end if you wish.
gt;
gt; Vaya con Dios,
gt; Chuck, CABGx3
gt;
gt;
gt;
gt; quot;jstephensonquot; wrote:
gt;
gt; gt; i am trying to sort a non sequential list of numbers mixed with numbers
gt; gt; marked with an asterisk in front, such as on a bank statement of checks:
gt; gt;
gt; gt; 1224
gt; gt; 1222
gt; gt; *1223
gt; gt; 1221
gt; gt; 1227
gt; gt; *1225
gt; gt; 1228
gt; gt;
gt; gt; and so on. When I sort, all of the numbers with an asterisk are grouped
gt; gt; together. I formatted them as text with the same result. Is there anything
gt; gt; i can do?