How to Convert Numbers and Dates to Bases Higher Than 36 in WPS Spreadsheet
Question details
The user wants to encode numeric values and dates into numeral systems higher than base 36 to generate extremely compact, single-character or short-string representations.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Creating compact, human-readable date stamps or identifiers to use within Windows filenames without hitting OS character restrictions or encountering font display issues.
- Observed behavior
- Standard bases (like base 36) use 0-9 and A-Z. Going higher requires mapping values to Unicode or other symbols, which often results in unsupported filename errors, unreadable text, or indexing failures in Windows.
Before generating compact timestamps for filenames, verify that the characters produced by your chosen base encoding do not contain restricted OS filename symbols (such as <, >, :, ", /, \, |, ?, or *) and use widely supported alphanumeric sets.
Encode Dates Separately Using Base 36 or Base 62
Base 36 (0-9, A-Z) and Base 62 (0-9, A-Z, a-z) provide the safest balance of high data compression and complete filename compatibility.
Instead of trying to compress an entire 8-digit date into a single Unicode character, which often results in unreadable output or OS errors, it is highly recommended to encode the year, month, and day as separate base 36 or base 62 characters. This keeps the string compact (typically 3 to 4 characters) while remaining completely human-readable with a standard lookup sheet.
In your spreadsheet, use the YEAR(), MONTH(), and DAY() functions in separate columns to break down the target date into three distinct numeric values.
Use a VBA script or a VLOOKUP table mapping 0-35 to digits 0-9 and letters A-Z to convert the year, month, and day individually into base 36 characters.
Use the CONCATENATE function or the & operator to merge the three converted characters into a single compact timestamp string.
Build a Custom Lookup Table for Higher Bases
If you absolutely must compress data further than base 62, create a strictly controlled custom lookup table using safe Unicode characters.
Easily Manage Data Conversions with WPS Spreadsheet
WPS Spreadsheet provides comprehensive built-in functions, including MOD, QUOTIENT, and VLOOKUP, as well as full VBA macro support to handle complex base conversions and custom date formatting effortlessly.
- 1. Open WPS Spreadsheet: Launch WPS Office and open the workbook containing the numbers or dates you wish to convert.
- 2. Set up your mapping table: Create a two-column table mapping numbers 0-61 to alphanumeric characters for a safe base 62 conversion.
- 3. Implement the conversion logic: Use built-in lookup functions, or open the WPS Macro Editor (ALT+F11) to write a custom VBA base-conversion script.
- 4. Generate compact strings: Drag the fill handle to apply your formulas across your dataset to instantly generate safe, compact timestamps.
Frequently Asked Questions
Why shouldn't I use random Unicode characters for bases higher than 62?
Many Unicode characters are explicitly restricted in Windows filenames, appear as unreadable blank boxes without specialized font support, or behave unpredictably during file searches. Sticking to alphanumeric characters ensures cross-system compatibility and readability.
How do I convert a standard date into a 3-character string?
Divide the date into a two-digit year, month, and day. Encode each of these three numeric parts into Base 36 using a lookup formula or VBA script, then concatenate them. This keeps the date extremely short while remaining decodable by humans.
Can I use Base 16 (Hexadecimal) instead of Base 36?
Yes, Base 16 uses digits 0-9 and letters A-F. While it requires more characters to represent the same number compared to Base 36, it is natively supported by most programming systems and provides perfectly safe strings for filenames.
