Sorting

[Daniell]

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,

 

[Alok Joshi]

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

 

[David McRitchie]

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

 

[Daniell]

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

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

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,

 

[David McRitchie]

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

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

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

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,