custom formating cells that are text & numbers in excel 2000

[MJamison07]

I have a column of items numbers in the following text format:

W-CA1
W-CA10
W-CA11
W-CA2
W-CA20

I want them to sort so that W-CA2 would come before W-CA10.

I was tried to do it through custom formatting (changing W-CA1 to W-CA01), so I can change the whole column at one time. Needless to say, I've been unsuccessful.

I would appreciate any help.

Thanks.
MJamison

 

[TonyJollans]

Hi MJamison07,

Easiest way is probably to create another column. If your item numbers are in column A, then in B1 enter =VALUE(MID(A1,5,LEN(A1)-4)) and copy down as far as your data goes. Then sort on column B. If your item numbers don't all begin with the same characters you may have to create a third column, but the general idea is the same. If you have trouble please post back with more sample data.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[KenWright]

Assuming your max value apart from the W-CA bit is 99 or less, then with your data starting in A1, in B1 put teh following and copy down:-

=LEFT(A1,4)&TEXT(RIGHT(A1,LEN(A1)-4),"00&quot

or even just

="W-CA"&TEXT(RIGHT(A1,LEN(A1)-4),"00&quot

When done, simply copy Col B and paste special as values over Col A. Delete what was in B and it will now sort normally.

If it already does, or could potentially go above 2 digits for the numeric, then trap it now and use:-

=LEFT(A1,4)&TEXT(RIGHT(A1,LEN(A1)-4),"000&quot

or

="W-CA"&TEXT(RIGHT(A1,LEN(A1)-4),"000&quot

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[SkipVought]

MJamison,

If you understand the DIFFERENCE between NUMERIC TEXT and NUMBERS, it will help you to conceptualize a solution in the future.

We all know that 2 sorts before 10, right? Well the NUMBER 2, which is binary 0000010 is smaller than 10 which is binary 0001010.

HOWEVER...

the character "2" is 00110010
the character string "10" is 00110001 00110000

So if we compare these two values...

"2"  00110010 
"10" 00110001 00110000

So now it's easy to see how "10" sorts before "2"

To make numeric character strings sort properly, you need to PREFIX the string with one or more zeroes depending on the MAX LENGTH of any numeric character string...

"02"
"10"

So if the MAX LENGTH numeric character string is 4...

"0002"
"0010"
"1234"

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884