Way to format column number to sort by number then letter?

K Micka

Hi,

Hopefully a very basic question - how do I format a column so that a number with a letter added will sort after the original number. So similar to how a "16.5" would come after a "16", I want a "16A" to come after a "16". Currently when sorted it is being thrown to the end of my numbering system (see below). Any help is appreciated!

 

Greg Gates

Unfortunately, the answer is no. At least, not with how your sheet is currently structured. Smartsheets treats pure numbers differently from anything with a letter in it, so the "text" (as Smartsheets sees it) will be organized alphabetically, and always come after all of your actual numbers.

 

This might not be ideal, but you could work around this by separating the Shot column into two columns - one with the number and one with the optional letter. Then when you sort, you can sort by both columns like this:

 

I hope that helps! I know it isn't ideal and it might not work if you're already using Smartsheet's maximum 3 levels of sorting, but I'm not sure of any other workaround right now.

Benjamin Walker

If you convert all the numbers to strings by using =Shot1+"" or adding a ' preceding the values your sort should work as expected!

 

If you have issues let me know

Greg Gates

As long as you don't go into the 100s, Benjamin's answer is definitely right! If you go into the 100s though, treating everything as text will group the 100s down with the 1s and 10s.