About Us

Bhuvanesh

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.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Trailing zeros	Gatz	Excel Worksheet Functions	5	April 30th 09 08:34 PM
Trailing Zeros	aftamath77	Excel Discussion (Misc queries)	3	October 7th 08 07:40 PM
Keep numbers as entered with trailing zeros	Allie	Excel Discussion (Misc queries)	2	August 3rd 06 06:39 PM
Keep Trailing Zeros When Convert To Text	Todd G	Excel Discussion (Misc queries)	2	March 23rd 06 03:16 PM
Trailing zeros are dropping off	mjd918	Excel Programming	0	October 18th 04 09:27 PM

Menu

About Us