I have an excel report with the time expressed in written text in Column A. (example: 2h 30m 14s). I would like to format in Column B as 02:30:14. Can I use Left, Mid, and Rght formulas together to achieve this?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Excel Formula
- Thread starter sunnylv
- Start date
Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 72,286
- Office Version
- 365
- Platform
- Windows
Welcome to the Board!
For a value in cell A1:
Note that will return a Text value. If you need it to be a valid time value that you can do math on, use this version:
and simply apply a custom format on the cell of "hh:mm:ss"
For a value in cell A1:
Excel Formula:
=TEXT(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"h ",":"),"m ",":"),"s","")),"hh:mm:ss")Note that will return a Text value. If you need it to be a valid time value that you can do math on, use this version:
Excel Formula:
=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"h ",":"),"m ",":"),"s",""))
Upvote
0
Solution
Scott Huish
MrExcel MVP
- Joined
- Mar 17, 2004
- Messages
- 20,339
- Office Version
- 365
- Platform
- Windows
Since you have Excel 365:
=--TEXTJOIN(":",,TEXTSPLIT(A1,{"h ","m ","s"}))
Format as hh:mm:ss
=--TEXTJOIN(":",,TEXTSPLIT(A1,{"h ","m ","s"}))
Format as hh:mm:ss
Last edited:
Upvote
0
