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