如果P列每个单元格的数据个数是相同的则公式可以如下
A1单元格输入公式
=TRIM(MID(SUBSTITUTE(OFFSET(P$1,INT((ROW(A1)-1)/(LEN(P$1)-LEN(SUBSTITUTE(P$1,",",""))+1)),),",",REPT(" ",99)),(1+MOD(ROW(A1)-1,LEN(P$1)-LEN(SUBSTITUTE(P$1,",",""))+1))*100-99,100))
下拉复制公式
B1单元格输入公式
=OFFSET(Q$1,INT((ROW(A1)-1)/(LEN($P$1)-LEN(SUBSTITUTE($P$1,",",""))+1)),)
右拉复制公式至E列,再下拉复制公式,
并分别设置单元格格式为数据所需要的日期时间格式
或者
B1公式为
=TEXT(OFFSET(Q$1,INT((ROW(A1)-1)/(LEN(P$1)-LEN(SUBSTITUTE(P$1,",",""))+1)),),"e/m/d h:mm")
C1公式为
=TEXT(OFFSET(R$1,INT((ROW(A1)-1)/(LEN($P$1)-LEN(SUBSTITUTE($P$1,",",""))+1)),),"e/m/d h:mm")
D1公式为
=TEXT(OFFSET(S$1,INT((ROW(A1)-1)/(LEN(P$1)-LEN(SUBSTITUTE(P$1,",",""))+1)),),"h:mm")
E1公式为
=TEXT(OFFSET(T$1,INT((ROW(A1)-1)/(LEN(P$1)-LEN(SUBSTITUTE(P$1,",",""))+1)),),"h:mm")
下拉复制公式
如果确定P列每个单元格内的数据个数是固定的11个,则上面所有公式中的语句"(LEN(P$1)-LEN(SUBSTITUTE(P$1,",",""))+1"可以直接改成11
如A1公式改成
=TRIM(MID(SUBSTITUTE(OFFSET(P$1,INT((ROW(A1)-1)/11),),",",REPT(" ",99)),(1+MOD(ROW(A1)-1,11))*100-99,100))
B1公式改成
=TEXT(OFFSET(Q$1,INT((ROW(A1)-1)/11),),"e/m/d h:mm")
C1公式改成
=TEXT(OFFSET(R$1,INT((ROW(A1)-1)/11),),"e/m/d h:mm")
D1公式改成
=TEXT(OFFSET(S$1,INT((ROW(A1)-1)/11),),"h:mm")
E1公式改成
=TEXT(OFFSET(T$1,INT((ROW(A1)-1)/11),),"h:mm")
温馨提示:答案为网友推荐,仅供参考