Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sorting
I have a field that can be three for four character. The problem is when I
sort the column it does not sort correct. What I need is to place a 0 in the field that has three digits in it. Is there a funtion that check for the number of digits and places a zero in the fileds that are to small? Thanks in advance for the help, |
#2
|
|||
|
|||
Hi,
If the data is in Column A, use this formula in Column B(assuming that the maximum width is 4 characters) =REPT(0,4-LEN(A1)) & A1 Alok "Daniell" wrote in message ... I have a field that can be three for four character. The problem is when I sort the column it does not sort correct. What I need is to place a 0 in the field that has three digits in it. Is there a funtion that check for the number of digits and places a zero in the fileds that are to small? Thanks in advance for the help, |
#3
|
|||
|
|||
Hi Daniel,
Perhaps you should include an example of your data, and how they should appear once sorted, but it sounds like you have a mixture of text and of numbers. If you had all numbers they would be sorted numerically. You can use =ISTEXT(A2) to test. Take a look at my page, perhaps there is already an answer there. http://www.mvps.org/dmcritchie/excel/sorting.htm -- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Daniell" wrote in message ... I have a field that can be three for four character. The problem is when I sort the column it does not sort correct. What I need is to place a 0 in the field that has three digits in it. Is there a funtion that check for the number of digits and places a zero in the fileds that are to small? Thanks in advance for the help, |
#4
|
|||
|
|||
Hi,
The column I am attempting to sort is a number that I use a function to fill the cell with. For example in column C you have 103 so I use the =left(c1,len(c1)-2) to put the number 1 in column 1. In column B I use the function =right(c2,2). Could it be the function that is messing up the sort. This is what the data looks like: A B C 1 03 103 10 04 1004 3 09 309 4 06 406 22 99 2299 "David McRitchie" wrote: Hi Daniel, Perhaps you should include an example of your data, and how they should appear once sorted, but it sounds like you have a mixture of text and of numbers. If you had all numbers they would be sorted numerically. You can use =ISTEXT(A2) to test. Take a look at my page, perhaps there is already an answer there. http://www.mvps.org/dmcritchie/excel/sorting.htm -- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Daniell" wrote in message ... I have a field that can be three for four character. The problem is when I sort the column it does not sort correct. What I need is to place a 0 in the field that has three digits in it. Is there a funtion that check for the number of digits and places a zero in the fileds that are to small? Thanks in advance for the help, |
#5
|
|||
|
|||
Hi Daniel,
You would use =LEFT(C1,1) to get the leftmost character of C1 length has nothing to do with anything. If C1 were empty your A1 with the formula would have a zero length string but would still be text. Hope you did look at my page on sorting, it should make clear difference between numbers and text. . --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Daniell" wrote in message ... Hi, The column I am attempting to sort is a number that I use a function to fill the cell with. For example in column C you have 103 so I use the =left(c1,len(c1)-2) to put the number 1 in column 1. In column B I use the function =right(c2,2). Could it be the function that is messing up the sort. This is what the data looks like: A B C 1 03 103 10 04 1004 3 09 309 4 06 406 22 99 2299 "David McRitchie" wrote: Hi Daniel, Perhaps you should include an example of your data, and how they should appear once sorted, but it sounds like you have a mixture of text and of numbers. If you had all numbers they would be sorted numerically. You can use =ISTEXT(A2) to test. Take a look at my page, perhaps there is already an answer there. http://www.mvps.org/dmcritchie/excel/sorting.htm -- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Daniell" wrote in message ... I have a field that can be three for four character. The problem is when I sort the column it does not sort correct. What I need is to place a 0 in the field that has three digits in it. Is there a funtion that check for the number of digits and places a zero in the fileds that are to small? Thanks in advance for the help, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
Sorting Spreadsheet with Merged Fields | Excel Discussion (Misc queries) | |||
Sorting problem | Excel Discussion (Misc queries) | |||
sorting question | Excel Discussion (Misc queries) | |||
Macro for sorting different rows | Excel Worksheet Functions |