I received a question today on how to sort a list of strings which was actually rather simple to accomplish.
The following list is the original sorting:
The requested list after sorting should look like this:
What I first did was to create a separate column to contain the length of each cell. There is a function in Excel which easily calculates the length of a string: LEN(). Now I had two columns, the first unsorted string column and the second length (of the first column string) column. As you probably have realised by now it was a simple matter of sorting the second column instead of the first for the strings to sort by length.
If interested in seeing it I have attached the test sheet for you to play around with:
8 Responses to Excel sort by length of string
JK
September 22nd, 2008 at 11:54
Thank you very much, this made things very easy for me! Especially the example file as syntax is different from english in other languages…
mr-euro
September 23rd, 2008 at 23:02
@JK
I thought Excel simply used the same English syntax for all formulae, even on the translated versions.
Hari
September 8th, 2011 at 10:24
Thank you it worked well…
Niki Kole
October 15th, 2011 at 21:25
Just great and simple! Exactly wha I needed! 😉
Sutha Sampanthar
January 27th, 2012 at 11:30
Many+thanks+for+this+as+it+was+just+what+I+needed.
seo consultant - Anukant
March 3rd, 2013 at 13:27
i love it.. friend.. from last five day i have been working on this.. but i could not find it.. i thank you
not only thank you….
thank you thank you thank you thank you thank you thank you
now i found the solution.
Cowpatty Bill
May 21st, 2013 at 19:48
This is good stuff. Let me take it a step further. How can I have it sort based on the width of the data? For example:
icicle – 6 letters long
wagon – 5 letters long
icicle has more letters but wagon is wider because of the font. How can I sort data based on width, not number of letters? Changing the font is not an option.
Shane
October 30th, 2015 at 05:41
Excellent solution – saved me heaps of time identifying a spurious entry for deletion in a 100,00 item list: thanks!