请问我想提取“ 加油：531.49元 78.82升”里面的加油金...

zsljm · 发表于 2021-10-24 09:26

请问老师我想提取“圈存金额：0.0圈提金额：0.0 加油：531.49元 78.82升”内容里面的加油金额531.49和加油量78.82数值，如何写公式，谢谢。

liujing_hotwind · 发表于 2021-10-24 09:57

本帖最后由 liujing_hotwind 于 2021-10-24 10:14 编辑

=TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$1,"油：","/"),"元 ","/"),"升","/")),"/",REPT(" ",100)),100*COLUMN(A1),100))
右拉

zsljm · 发表于 2021-10-24 10:35

liujing_hotwind 发表于 2021-10-24 09:57
=TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$1,"油：","/"),"元 ","/"),"升","/")),"/ ...

提取成功，解决了大难道，感谢！

249773986 · 发表于 2021-10-25 07:32

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(MID($A1,MAX(IFERROR(FIND("：",$A1,ROW(INDIRECT("1:"&LEN($A1)))),""))+1,99),"元 ",REPT(" ",99)),"升",""),COLUMN(A1)*99-98,99)) 三键结束，横拉，如果要纵向下拉的话把COLUMN(A1)改成ROW(A1)

zsljm · 发表于 2021-10-25 10:29

249773986 发表于 2021-10-25 07:32
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(MID($A1,MAX(IFERROR(FIND("：",$A1,ROW(INDIRECT("1:"&LEN($A1)))),"")) ...

提示公式错误

zsljm · 发表于 2021-10-25 10:37

liujing_hotwind 发表于 2021-10-24 09:57
=TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$1,"油：","/"),"元 ","/"),"升","/")),"/ ...

请问我在表格前面插入列后公式就失效了，为什么？

liujing_hotwind · 发表于 2021-10-25 10:38

zsljm 发表于 2021-10-25 10:37
请问我在表格前面插入列后公式就失效了，为什么？

上传附件看一下

liujing_hotwind · 发表于 2021-10-25 10:44

liujing_hotwind 发表于 2021-10-25 10:38
上传附件看一下

=TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($B$1,"油：","/"),"元 ","/"),"升","/")),"/",REPT(" ",100)),100*COLUMN(A1),100))
插一列单元格会变成COLUMN(B1),改回来COLUMN(A1)就可以了

249773986 · 发表于 2021-10-25 10:54

zsljm 发表于 2021-10-25 10:29
提示公式错误

附件已上传~

zsljm · 发表于 2021-10-25 10:55

liujing_hotwind 发表于 2021-10-25 10:44
=TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($B$1,"油：","/"),"元 ","/"),"升","/")), ...

详见测试表格

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

[求助] 请问我想提取“ 加油：531.49元 78.82升”里面的加油金...