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

[MrTown]

How can I create a sort with helper columns or some other methods t
achieve the following?
I would like to sort by evaluating each placement value from left t
right and place them in descending order as if they are all the sam
length of character string... I have tried many helper columns but can'
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!!

 

[dacromley]

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

 

[dcxtxtxtxt]

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,"-","~")

 

[Bhuvanesh]

Two problems. The first is that Excel (2007 anyway) seems to persist i
separating out the numbers and putting 12 before 111. This can be fixe
by putting a character at the front. The second problem is that ther
is something strange about the "-" symbol when Excel sorts. So replac
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 digi
numbers assuming the value is in column A, and then afterwards you ca
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 formul
$L$2:$M$4
here you would mention the number of trailing zeros to use to complete
digits (precede with a space filler)

1 '0
2 '00
0 '

I hope this works for you