2

I am making an expenses spreadsheet in Google Docs and I need to list the numbers of the attachments for each entry. Sometimes this is just one number, sometimes it is several.

I can make this column be formatted as text, but I want to make the spreadsheet sortable by the first attachment number listed. When the cell contents are "1", "2&3", "4-7" etc. "11" is sorted before "2", which is a problem.

The one solution I see is writing the numbers instead as "001", "002&003", "004-007" etc. and still formatting as text, but this strikes me as unnecessarily cumbersome. Is there a better way?

Improve this question

1 Answer 1

Reset to default
1

UPDATE

Changed the formula to accept the ampersand as well

The following formula will create a sorted column:

=SORT(ARRAYFORMULA(VALUE(INDEX(SPLIT(A:A;"-&"),0,0))))

See example file I've created: Sort-able text column

Improve this answer
5
  • Thanks, Jacob! This looks like an even better solution. I can't quite get it to work, though, cf. example.
    – Nagel
    Apr 16, 2013 at 15:32
  • @Nagel If you share a doc with us, we will make it work!
    – Jacob Jan Tuinstra
    Apr 16, 2013 at 15:36
  • Ok. I have now made my example spreadsheet editable by anyone.
    – Nagel
    Apr 16, 2013 at 20:19
  • @Nagel Made some changes. Set column A to plain text. Otherwise the SPLIT function doens't work. Added a hyphen ("-") to the SPLIT as well.
    – Jacob Jan Tuinstra
    Apr 16, 2013 at 20:27
  • 1
    Marvelous! Great to see that it's working.
    – Nagel
    Apr 17, 2013 at 11:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.