请教各位大佬，怎样提取下面的数据

在用逗号分隔的一组串数据填在一个excel格子里，怎样快速提取第几个数据，就像下面这样的一串数据

08:30,7.88,33.90,16.28,12.11,22.03,15.75,7.39,20.59,0.00,25.41,22.96,12.39,2.46,3.39,8.27,23.84,18.08,2.95,10.14,10.90,6.29,6.75,12.71,13.55,4.88,0.75,3.81,4.06,2.29,4.47,8.15,19.56,16.39,18.83,28.65,16.69,9.74,20.18,5.76,6.64,4.33,6.63,7.12,8.42,2.73,3.71,23.04,83.96,41.40,2.47,26.65,3.70,16.94,5.55,41.85,0.00,26.84,10.37,44.17,2.19,31.73,8.72,11.28,18.87,19.25,30.88,6.80,10.65,4.46,4.41,9.64,11.86,43.02,10.75,24.94,3.26,7.48,4.91,24.57,51.07,0.00,3.01,34.76,0.00,24.38,49.58,20.90,11.49,23.43,1.20,1.60,10.76,1.02,0.17,1.81,7.06,9.30,22.31,0.00,34.66,13.42,8.13,8.17,24.41,2.09,0.00,21.71,22.61,0.00,0.00,0.00,2.16,0.00,0.00

比如要提取第五个数据，那就是12.11

用分列拆分开来
用公式也能做，没分列好用。

=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),A2*99-98,99))
A1为原文本单元格
A2为提取第几个数

=IF(A2-1=0,LEFT(A1,FIND(",",A1,1)-1),IFERROR(MID(SUBSTITUTE(A1,","," ",A2-1),FIND(" ",SUBSTITUTE(A1,","," ",A2-1),1)+1,FIND(" ",SUBSTITUTE(A1,","," ",A2),1)-FIND(" ",SUBSTITUTE(A1,","," ",A2-1),1)-1),IFERROR(RIGHT(A1,LEN(A1)-FIND(" ",SUBSTITUTE(A1,","," ",A2-1),1)),"超出限制")))
A1为原文本单元格
A2为提取第几个数

Function sp(rng As Range, r As String, k As Integer)
arr = Split(rng, r)
sp = arr(k - 1)
End Function
自定义函数sp(目标单元格，拆分字符，第几个)

textsplit

扫二维码下载贴吧客户端

下载贴吧APP
看高清直播、视频！

6回复贴，共1页

<<返回excel吧

分享到:

日	一	二	三	四	五	六