How to Convert Text Like '19 min 3 sec' to Time in Excel
Question details
The user needs to transform text strings formatted as 'X min Y sec' into standardized Excel time values or a pure decimal number representing total minutes.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Data cleaning and time-based calculations.
- Observed behavior
- Time duration data is stored as unstructured text, preventing users from performing mathematical calculations or applying standard time cell formatting.
Ensure your text strings follow a consistent spacing and naming pattern (e.g., '# min # sec') before applying the formulas, as inconsistencies can lead to #VALUE! errors.
Convert Text to a Standard Excel Time Value
Use a combination of the TIME, LEFT, FIND, and MID functions to extract numeric characters and construct a recognizable time format.
This method locates the positions of 'min' and 'sec' in your text string, pulls out the exact numbers associated with them, and uses the TIME function to compile them into hours, minutes, and seconds. Because hours are missing in the original text, the formula defaults to 0 hours.
Select an empty cell next to your text data (for example, B1). Type the formula: `=TIME(0,LEFT(A1,FIND(" min",A1)-1),MID(A1,FIND(" min",A1)+5,FIND(" sec",A1)-FIND(" min",A1)-5))` and press Enter.
Right-click the result cell and select 'Format Cells'. Navigate to the 'Custom' category and enter `[h]:mm:ss` or `mm:ss` to display the extracted time correctly.
Convert Text into Total Decimal Minutes
Extract the minutes and seconds as standalone numeric values to calculate a total decimal number, which is ideal for wage or rate calculations.
Use Microsoft 365 Text Functions
If you are using Microsoft 365, you can utilize newer, more streamlined text functions like TEXTBEFORE to parse the string.
Process Time Data Effortlessly with WPS Spreadsheet
WPS Spreadsheet provides robust data calculation tools and fully supports standard Excel formulas like TIME, FIND, and MID, allowing you to clean unstructured text seamlessly.
- 1. Open Your Dataset: Launch WPS Spreadsheet and open the document containing your text-based time strings.
- 2. Apply the Formula: Select an empty adjacent cell and paste the TIME extraction formula provided in the recommended solution.
- 3. Apply Cell Formatting: Right-click the cell, select 'Format Cells', and apply a custom time format such as [h]:mm:ss.
- 4. Drag to Fill: Click and hold the fill handle at the bottom right of the cell, then drag downwards to apply the time conversion to the rest of your data.
Frequently Asked Questions
Why does my formula return a #VALUE! error when converting text to time?
A #VALUE! error usually occurs if the text string does not perfectly match the expected pattern. For instance, if there are double spaces, misspellings like 'mins' instead of 'min', or missing seconds data. You can use the TRIM function to remove extra spaces before extracting.
How do I sum up the converted times across multiple rows?
Once you have successfully converted the text into valid Excel time values using the TIME function method, you can add them together using the standard =SUM() function. Make sure the cell containing the total is formatted with the custom format [h]:mm:ss so it can display sums that exceed 24 hours without rolling over.
Can I use the SUBSTITUTE function instead of FIND and MID?
Yes. A simpler alternative if your data format is extremely consistent is replacing text labels with time delimiters. You can use a formula like =TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A1,"min",":"),"sec","")), provided it outputs a standard "HH:MM:SS" recognizable string.
