Convert Text Timestamps with Milliseconds into Excel Times in WPS Office
Question details
The user needs to convert timestamp values like 12:34:56.000 that are stored as text into real time values in WPS Office Spreadsheets so they align correctly and can be used in calculations.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Time values with milliseconds were imported or pasted into a spreadsheet and are left-aligned as text, often after an update or when the source format changes.
- Observed behavior
- The affected cells are left-aligned, treated as text strings, and cannot be used in time calculations until converted.
Open the affected workbook in WPS Spreadsheets and identify the column containing left-aligned time values such as 12:34:56.000. Confirm the cells are stored as text by selecting one cell and checking that it does not use a date or time number format.
Convert Text Timestamps with Text to Columns
Use this when you need the fastest built-in fix for an entire column of millisecond timestamps stored as text.
WPS Spreadsheets treats the Text to Columns finish action as a reparsing command, which can change text entries into numeric time values even without a delimiter.
After conversion, the default display may not show milliseconds until you apply a custom format.
Click the column header or drag to select the cells containing values such as 12:34:56.000.
Go to the Data tab and click Text to Columns.
Choose Delimited, click Next, and then click Finish without selecting any delimiter.
Press Ctrl+1, select Custom, enter hh:mm:ss.000 in the Type box, and click OK.
Use a Formula to Convert Timestamps into Calculable Times
Use this when you want to keep the original text values and create a separate converted time column.
Convert Millisecond Text Timestamps to Real Time Values in WPS Office
WPS Office Spreadsheets provides the same Text to Columns and custom number formatting workflows as Excel, so you can convert text timestamps such as 12:34:56.000 into real time values and continue calculating without retyping data.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office and open the workbook that contains the millisecond timestamps.
- 2. Select the affected timestamp column: Click the column header or select the cells containing values such as 12:34:56.000.
- 3. Open Text to Columns: Go to the Data tab and click Text to Columns.
- 4. Finish the conversion: Choose Delimited, click Next, and click Finish without selecting any delimiter.
- 5. Apply the millisecond number format: Press Ctrl+1, select Custom, enter hh:mm:ss.000 in the Type box, and click OK.
Frequently Asked Questions
Why are timestamps such as 12:34:56.000 left-aligned in my spreadsheet?
Left alignment usually means the values are stored as text. WPS Spreadsheets treats them as strings instead of numbers, so they cannot calculate until you convert them with Text to Columns or a formula.
Does Text to Columns keep milliseconds when converting time text?
Yes, but WPS Spreadsheets may not display milliseconds automatically. After using Data > Text to Columns, apply the custom number format hh:mm:ss.000 to show the millisecond part.
Can I convert text timestamps without changing the original column?
Yes. Add a helper column and use =TIMEVALUE(LEFT(A1,8))+VALUE(RIGHT(A1,3))/86400000, then fill down and format the helper column as hh:mm:ss.000.
What if the formula returns #VALUE!?
Check that the text timestamp is exactly 11 characters in hh:mm:ss.000 form and that A1 contains text, not a number. Extra spaces or a date prefix may require cleaning the source text first.
How do I display milliseconds in WPS Office Spreadsheets?
Select the converted cells, press Ctrl+1, go to Custom, type hh:mm:ss.000 in the Type box, and click OK.
