Sorting problem with large data set

**Infinity666** · 10-20-2009, 11:03 AM

Greetings!

This seems like it should be such an easy fix and I'm sure it is, but I can't can't figure it out. I have a series of data that I want sorted. Howerver, Sorting gives me the odd result of the follow:

0801952-1
0801952-10
0801952-11
0801952-12
0801952-13
0801952-14
0801952-15
0801952-16
0801952-17
0801952-18
0801952-19
0801952-2
0801952-20
0801952-3
0801952-4
0801952-5
0801952-6
0801952-7
0801952-8
0801952-9

Now I understand why it's doing it. But is there an easy way to fix this problem without going back into my data and making the trailing numbers -0X's? My set of data is near 1600 so you can understand why I don't want to try and fix this by hand.

Thanks in advance for any help!

**DonkeyOte** · 10-20-2009, 11:13 AM

In an adjacent column create revised versions, eg assuming trailing number is in range 1-999 then based on your sample:

=LEFT(A1,FIND("-",A1))&TEXT(RIGHT(A1,LEN(A1)-FIND("-",A1)),"000")
where A1 contains original (adjust 000 to meet requirements - ie if range 1-99999 then use 00000 etc...)

Copy and paste results over formulae and sort by this column (or copy over original values if preferred)

**Infinity666** · 10-20-2009, 11:34 AM

Awesome, thanks!

Is there a source I could read through to give me a better idea of what that formula is actually doing? I've never worked with the LEFT/RIGHT or FIND functions so I'd like to learn what is actually going on. Or if you have a moment to walk me though it that would be great also. But, I realise you probably have better things to be doing

.

**DonkeyOte** · 10-20-2009, 11:53 AM

Be sure to check out the XL help files on each.

LEFT(text,numchars) -> returns specified numbers of chars from start of a text string

RIGHT(text,numchars) -> as above but from the end of a text string

FIND(find text, within text, [start num]) -> starting position of find text within "within text" with FIND searching "within text" from character specified as start num onwards ([start num] is optional - default 1)
Note: find is case sensitive unlike Search ... irrelevant in this instance given a hyphen is not affected by case

In essence the formula provided previously :

a) Extracts the string up to and including hyphen.

b) Takes the number that appears post hyphen and formats value to 000
(ie 1 becomes 001, 10 becomes 010, 100 remains as before)

c) Concatenates the two strings a) & b)

**Infinity666** · 10-20-2009, 11:56 AM

I just went through the formula peice by peice and figured it out myself. It's actually not as complex as I originally thought. Thanks again for such a quick and accurate responce!