Sorting data that is a combination of text and numbers, and of varying lengths

Posted by Tim on May 06, 2001 9:31 PM

I am having problems sorting data alpha-numerically. For Example:
This is the result of an ascending sort function of a particular column of data.

CE1
CE10
CE4
R14
R2
R4
R5

I want the result to be:
CE1
CE4
CE10
R2
R4
R5
R14

What are the different ways to format the data so that it sorts according to my needs?

I can think of two ways, but do not know how to actually carry out these ideas:
1) Separate the alpha characters from the numerics - put in two columns - then sort the two columns accordingly

2)Convert the data to a specific string length by adding the leading zeros, and then sort (i.e. modify R2 to R002)

Any other suggestions, with solutions of course.

Thanks much.

Posted by Dave Hawley on May 06, 2001 9:46 PM

Hi Tim

This can be done as you suggest by sepertaing the Numeric portion from the Text portion. Assume the text you want to seperate is in column A in B1 put:
=IF(ISERR(VALUE(RIGHT(A1,2))),VALUE(RIGHT(A1,1)),VALUE(RIGHT(A1,2)))

This will extract the number. If you also have numbers greater than 2 digits you will need to add another condition.

Now in cell C1 put:
=LEFT(A1,LEN(A1)-LEN(B1))

Copy both of these down as far as required, then select Column B:C and Copy-PasteSpecial as Values over the top of themselves. no simply sort Columns A:C by Column C first then by Column B

Dave
OzGrid Business Applications

Posted by Kevin James on May 06, 2001 10:10 PM

Hello again Tim,

Its good to see Dave has answered you. The suggestion I offered you before will also work.

If the column you are sorting is A, then enter this in a blank column. For this illustration, we'll assume B. In B1 enter: LenOfA as a heading. Starting with B2, enter the formula:
=LEN(A2)

Copy this down.

Now sort by column B as first criteria and column A as second criteria.

Take care,

Kevin