4

We use a number system like software versioning or IP addresses for business requirements, using decimal points to separate significant number sets.

Spreadsheets don't handle this well because when doing 1.8, 1.9, 1.10, 1.11 it treats these numbers as decimals and 1.10 is equal to 1.1.

I want to be able to type and sort these numbers without sheets auto-formatting important information away.

5

While there are answers to the sorting problem such as: How can I sort in Google Sheets by cells containing section (or version) numbers?

I've found the simplest method is to keep each significant number in its own column and concatenate them into the final number. Then you can sort on the source columns.

screenshot of sortable concatenation

| improve this answer | |
1

To prevent entered text from being converted to a number, precede it by a single quote: for example, entering '1.10 will have the content of the cell displayed as 1.10.

Importantly, the quote itself is not shown in the sheet, and is not a part of cell's content.

This solves the formatting issue but not sorting: since strings are sorted alphabetically, 1.10 precedes 1.2 in the sort.

| improve this answer | |
  • Yeah, you also can't do auto-filling stuff with this method. Using multiple columns you can drag-fill sequential numbers quickly. Still a useful reminder in context though. – dubj May 12 '16 at 15:08

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.