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?
Best Answer
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