如何将不同长度的文本转换为时间数据

zhang_seal · 发表于 2011-1-15 21:44

各位大虾，有如下几组不同长度的文本，请问如何用一个公式将之转换为时间数据？

13小时23分13秒  13:23:13
12小时20分3秒 12:20:03
1小时13分49秒 01:13:49
42分33秒       00:42:33
4分10秒       00:04:10
29分3秒       00:29:03

谢谢了！

山桑弓 · 发表于 2011-1-15 21:55

如果数据在A1:A100中,B1公式可为
=--(REPT("0时",--ISERR(FIND("小时",A1)))&SUBSTITUTE(A1,IF(ISERR(FIND("小时",A1)),"","小"),))
自动填充下拉,单元格格式设置为时间.

博爱风雨同舟 · 发表于 2011-1-15 21:58

=MID(A1,1,FIND("小时",A1)-1)&":"&MID(A1,FIND("时",A1)+1,FIND("分",A1)-FIND("时",A1)-1)&":"&MID(A1,FIND("分",A1)+1,FIND("秒",A1)-FIND("分",A1)-1)

山桑弓 · 发表于 2011-1-15 22:10

再来一个
=--SUBSTITUTE(RIGHT("0小时"&A1,LEN(A1)+2*(LEN(A1)<7)),"小",)

wshcw · 发表于 2011-1-15 22:11

公式:
=-LOOKUP(,-({0,"0时"}&SUBSTITUTE(A1,"小",)))
通用公式(如:7秒等格式也能转换)
=-LOOKUP(,-SUBSTITUTE(TEXT({0,"0时"}&SUBSTITUTE(A1,"小",),"@"),"时","时0分"))

[ 本帖最后由 wshcw 于 2011-1-15 22:15 编辑 ]

gj98122 · 发表于 2011-1-15 22:11

现将42分33秒等用="0小时"&对应单元格
格式统一后
=TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,"小时",":"),"分",":"),"秒",""),"HH:MM:SS")

tkgg93 · 发表于 2011-1-15 22:12

=TEXT(IF(COUNTIF(A1,"*小*")=0,"0时","")&SUBSTITUTE(A1,"小",""),"hh:mm:ss")

609 · 发表于 2011-1-15 22:13

=TEXT(-IF(ISERR(FIND("小",A1)),-("0时"&A1),-SUBSTITUTE(A1,"小","")),"hh:mm:ss")

tkgg93 · 发表于 2011-1-15 22:19

原帖由 wshcw 于 2011-1-15 22:11 发表
公式:
=-LOOKUP(,-({0,"0时"}&SUBSTITUTE(A1,"小",)))
通用公式(如:7秒等格式也能转换)
=-LOOKUP(,-SUBSTITUTE(TEXT({0,"0时"}&SUBSTITUTE(A1,"小",),"@"),"时","时0分"))

学习版主的公式

wshcw · 发表于 2011-1-15 22:21

原帖由 609 于 2011-1-15 22:13 发表
=TEXT(-IF(ISERR(FIND("小",A1)),-("0时"&A1),-SUBSTITUTE(A1,"小","")),"hh:mm:ss")

6,7,8楼都不通用,遇到部分含有如8:12:13的纯时间格式混合就出错.

		自动登录	找回密码
密码			免费注册

[求助] 如何将不同长度的文本转换为时间数据