Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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