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?

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

  • Thanks, Jacob! This looks like an even better solution. I can't quite get it to work, though, cf. example. – Nagel Apr 16 '13 at 15:32
  • @Nagel If you share a doc with us, we will make it work! – Jacob Jan Tuinstra Apr 16 '13 at 15:36
  • Ok. I have now made my example spreadsheet editable by anyone. – Nagel Apr 16 '13 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 '13 at 20:27
  • 1
    Marvelous! Great to see that it's working. – Nagel Apr 17 '13 at 11:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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