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,

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,

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,

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,

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,