如何在Excel中将文本格式旅行时间转换为标准h:mm格式？

你的原始数据分为两种格式：带小时的（如1 hour 3 mins）和仅含分钟的（如46 mins），之前用SUBSTITUTE的问题在于未处理无小时的条目、个位数分钟未补前导零，以下是两个可行的公式解决方案：

方案1：统一格式后格式化 #

先给无小时的条目补全0 hour 前缀，再替换格式并转为标准时间格式：

=TEXT(SUBSTITUTE(SUBSTITUTE(IF(ISNUMBER(SEARCH("hour",[@[Travel Time]])),[@[Travel Time]],"0 hour "&[@[Travel Time]])," hour ",":")," mins","")*1,"h:mm")

逻辑说明： #

1. IF(ISNUMBER(SEARCH("hour",...))) 判断单元格是否含小时信息，无小时则补0 hour ，统一为X hour Y mins格式
2. 两次SUBSTITUTE将文本替换为X:Y形式
3. *1将文本转为数值，最后用TEXT(...)格式化为h:mm，自动补全分钟数的前导零

方案2：提取时分计算后格式化 #

直接提取小时和分钟数，计算总分钟数再转为时间格式：

=TEXT(IFERROR(LEFT([@[Travel Time]],FIND(" hour ",[@[Travel Time]])-1)*60+MID([@[Travel Time]],FIND(" hour ",[@[Travel Time]])+6,LEN([@[Travel Time]])-FIND(" hour ",[@[Travel Time]])-9),LEFT([@[Travel Time]],FIND(" mins",[@[Travel Time]])-1))/1440,"h:mm")

逻辑说明： #

1. IFERROR分别处理两种数据：
   • 含小时的：提取小时数转成分钟，加上提取的分钟数，总和除以1440（一天总分钟数）得到Excel可识别的时间值
   • 仅含分钟的：提取分钟数除以1440得到时间值
2. TEXT(...)将时间值格式化为h:mm格式

两个公式都能输出你需要的结果：

1:03
0:46
0:52
1:10

内容的提问来源于stack exchange，提问作者Bryce Bedah