Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Numbers stored as text causes problem with VLOOKUP | Excel Worksheet Functions | |||
Sorting numbers and text separately | Excel Discussion (Misc queries) | |||
Sorting order - text and numbers | Excel Discussion (Misc queries) | |||
Converting Numbers to Text properly | Excel Discussion (Misc queries) | |||
Sorting when some numbers have a text suffix | Excel Discussion (Misc queries) |