SORTING TEXT AND NUMBERS

J

jstephenson

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

CLR

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
 
E

Elkar

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
 
R

Ron Rosenfeld

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?
Click to expand...

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
 
J

jstephenson

thanks, that did it

Elkar said:
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
Click to expand...
 
J

jstephenson

thanks, that did it

CLR said:
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
Click to expand...
 
Top