Numerical sorting can be enforced in a column that contains numbers and text, whether the sort itself is actually numerical or alphabetical. This can be done by placing hidden leading zeroes in front of numbers containing less digits to make all numbers have the same number of digits. Hidden leading zeros can be added using;
<span style="display:none">add as many zero's here as needed</span>
Note that in the examples used below and in the table, the largest number used has 5 digits. More hidden leading zeroes need to be used if the largest number has more digits and less hidden leading zeroes need to be used if the largest number has less digits.
- Hidden zeroes can be placed in front of numbers so that 00022, 10,960, 00000, 00005, 00800, 00103, 09,654 will sort as 00000, 00005, 00022, 00103, 00800, 09,654, 10,960 and displays as 0, 5, 22, 103, 800, 9,654, 10,960.
- Numbers need to be in front of any letters or symbols (i.e., "00200 text" and "00077g" will work but "text 00200" and "$00077" wont work).
- If the sort is numerical then data with symbols (only or first, e.g., "#", #232, "?n/a", "(44g), etc), letters (only or first, e.g., "A", "A650g", "zhjz", "Nerys 200g", etc), blanks only and zeroes only will all be treated as if they were a single zero (and will not be sorted but grouped together before or after the numbers) but numbers with hidden leading zeroes (with or without letters and symbols after them e.g., "00022", "0022g", "22,000", "22,000#", "02,200", "02,200g Nerys and Granites", etc) will be sorted numerically.
- If the sort is alphabetical then the data will be sorted in the order
- blanks only, some symbols (only or first), numbers (only or first), other symbols (only or first) and letters (only or first)
- but the numbers (only and first) will be in numerical order because of the hidden leading zeroes.
- If the numbers are always before any symbols or letters then the numbers will always appear to be sorted numerically if the hidden leading zeros are added (even if the actual sort is numerical or alphabetical).
- Note that zeroes will need hidden leading zeroes to have them sort correctly (i.e., 00000).
- Blanks will be treated as a single zero and can be used when no data is available (instead of "-", "?", "n/a" or "0").
- In the table below see the last 4 columns for examples of using hidden leading zeros and not using them.
|