Sort by placement value from left to right & adding trailing zeros for short
 

How can I create a sort with helper columns or some other methods to achieve the following?
I would like to sort by evaluating each placement value from left to right and place them in descending order as if they are all the same length of character string... I have tried many helper columns but can't sort exactly as needed.

I would like to turn this:

1200
1205
1210
1211
1215
1218
1225
1245
1060-5
114-0-01
114-5-01
115-3-01
116-3-02
116-5-02
117-0-02
117-5-02
120-3-01
120-5-01
121-0-01
121-5-01
1225-3
123-0-01
123-5-01
124-0-01
124-5-01




INTO THIS, once sorted:

1060-5
114-0-01
114-5-01
115-3-01
116-3-02
116-5-02
117-0-02
117-5-02
120-3-01
120-5-01
1200
1205
121-0-01
121-5-01
1210
1211
1215
1218
1225
1225-3
123-0-01
123-5-01
124-0-01
124-5-01
1245




Thank you!!!

If you had a helper column and use the substitute(a1, "-", "*") formula to replace the "-" with "*" and then sort according to this column, this should do the job.

If it still gives you problems let us know

Sort by placement value from left to right & adding trailingzeros for short
 

How about:
=CONCATENATE(SUBSTITUTE(A1,"-","~"),LEFT("~~~~~~~~",8-LEN(A1)))
What a mess, uh?

Sort by placement value from left to right & adding trailingzeros for short
 

On Saturday, April 6, 2013 9:29:11 AM UTC+13, MrTown wrote:
How can I create a sort with helper columns or some other methods to

achieve the following?

I would like to sort by evaluating each placement value from left to

right and place them in descending order as if they are all the same

length of character string... I have tried many helper columns but can't

sort exactly as needed.

Two problems. The first is that Excel (2007 anyway) seems to persist in separating out the numbers and putting 12 before 111. This can be fixed by putting a character at the front. The second problem is that there is something strange about the "-" symbol when Excel sorts. So replace it with something else like the "~" suggested.

Make the helper column something like ="x"&SUBSTITUTE(A1,"-","~")

Use the below given function to convert all the values to six digit numbers assuming the value is in column A, and then afterwards you can sort them

=CONCATENATE(SUBSTITUTE($A2,"-","")*1,VLOOKUP(6-LEN(SUBSTITUTE($A2,"-","")),$L$2:$M$4,2,0))*1

in this you have to create a table for vlookup eg in above formula $L$2:$M$4
here you would mention the number of trailing zeros to use to complete 6 digits (precede with a space filler)

1 '0
2 '00
0 '

I hope this works for you.