Quote:
Originally Posted by
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.