Excel sort by length of string

12 Jun
2008

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:

  • caca
  • cocacola
  • ca
  • cacacacacola
  • c
  • cacacacacacacaac
  • cacaa

The requested list after sorting should look like this:

  • c
  • ca
  • caca
  • cacaa
  • cocacola
  • cacacacacola
  • cacacacacacacaac

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:

Length String Sorting

If you found this post interesting I recommend the following reading:


8 Responses to Excel sort by length of string

Avatar

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…

Avatar

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.

Avatar

Hari

September 8th, 2011 at 10:24

Thank you it worked well…

Avatar

Niki Kole

October 15th, 2011 at 21:25

Just great and simple! Exactly wha I needed! 😉

Avatar

Sutha Sampanthar

January 27th, 2012 at 11:30

Many+thanks+for+this+as+it+was+just+what+I+needed.

Avatar

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.

Avatar

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.

Avatar

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!

Comment Form

top