SORTING TEXT AND NUMBERS

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

[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



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

[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

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

[Ron Rosenfeld]

On Fri, 10 Feb 2006 11:57:20 -0800, "jstephenson"
<[email protected]> 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

[jstephenson]

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?

[jstephenson]

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?