Help about a text custom format, please

Cells containing something like 1234-56 or 1234-56.78.9 are going to be text strings, and number formatting is not going to do much with text strings. We will probably need a better understanding of exactly what you want to do here. Are you entering numbers but want them to appear as text strings? Are you entering text strings, but you want to display the text different from the entered text value?

If I enter 9 digit numbers (123456789) and then use a number format code like 0000"-"00"."00"."0, then I can get 123456789 to display as 1234-56.78.9, but be very clear in your mind that the underlying cell value of such a formatted number is still 123456789. Something like 1234 will display as 0000-01.23.4. Something like 123400000 will display as 1234-00.00.0.

Help us understand better what you are wanting to do here (what value are you wanting to enter into the cell and how do you want that value displayed). I'm not certain that number formatting alone will achieve what you want, but we can explore possibilities when we better understand your goals.

That does make sense. Is the real problem the sorting issue then? What it seems we have is a mix of numbers and text strings. As noted above, numbers are all smaller than text, so it appears that Excel is sorting all the numbers in increasing numeric order, then sorting all the text in increasing text order.

The usual solution I see to this is to parse the text so that each element is in its own cell/column/field as a number. Is that allowed? Text to columns can do this quite readily. One time through using "-" as the delimiter, and a second pass through using "." as the delimiter. Then, each element is in its own field as a number. Then call up the sort algorithm and tell it to sort first by the first column, then add level to tell it to sort by the second column, and so on for as many elements/columns/fields you have.

In order to get the correct sort, my version of Excel wanted 0s instead of blanks in the empty element spaces. On this small sample, that was not difficult to do manually, but, on a larger sample, that may get tedious to do manually. You might see if this is also going to be necessary in your version, and consider how to handle this issue.

Separating into columns was just the first possibility. I'm sure there are other approaches to solve this. Other than making a custom list (which would require you to know all of your possible combinations of elements), any approach that I can think of involves doing something to the data to turn it all to real numbers in a way that sorts correctly by numeric sort order, or turn all of the numbers to text strings in a way that they sort correctly by text sort order rules.

Whether you opt to use the Sort utility, or formulas to perform the sort, or however you decide to perform the sort, Excel is going to want something done to the data to get it into a form that Excel can sort correctly. If you don't like parsing into columns, I could see converting the inputs to decimal numbers like 1234.345504, 12, 145.01, 145.0102 etc. If you prefer a text sort, then rework the data into something that will sort correctly as text. What's your preference?

As for a formula based approach to sorting, I outlined the way I do it (numeric sort order only) in a post the other day https://www.excelforum.com/excel-gen...ml#post5649786 This part only works after getting the data into an all number format, so the main problem is still how to get the data from your raw inputs into something that Excel can sort.