SORTING TEXT AND NUMBERS
 

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?

SORTING TEXT AND NUMBERS
 

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 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



"jstephenson" wrote:

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?

SORTING TEXT AND NUMBERS
 

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,"*","")

Then sort by this new column.

HTH,
Elkar

"jstephenson" wrote:

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?

SORTING TEXT AND NUMBERS
 

On Fri, 10 Feb 2006 11:57:20 -0800, "jstephenson"
wrote:

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?

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

SORTING TEXT AND NUMBERS
 

thanks, that did it

"Elkar" wrote:

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,"*","")

Then sort by this new column.

HTH,
Elkar

"jstephenson" wrote:

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?

SORTING TEXT AND NUMBERS
 

thanks, that did it

"CLR" wrote:

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 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



"jstephenson" wrote:

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?