不规则空格间数据提取

我爱我嘉0926 · 发表于 2018-2-10 14:26

我需要在一组数据中提供空格间的字符，但组织中有些是多个空格，或者不规则的空格，数据如附件，请高手帮忙修改一下公式：=MID(A1,FIND("☆",SUBSTITUTE(A1," ","☆",1))+1,FIND("☆",SUBSTITUTE(A1," ","☆",2))-FIND("☆",SUBSTITUTE(A1," ","☆",1))-1)，谢谢！

jivy1212 · 发表于 2018-2-10 15:40

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2,"    "," "),"   "," ")," ",REPT(" ",50)),COLUMN(B1)*50-49,50)) 右拉下拉，你的源数据里有特殊空格吧。懒得去查哪种了，直接上粘贴进公式了

sayhi95 · 发表于 2018-2-10 15:41

不规则大概只能自己手动改吧

sayhi95 · 发表于 2018-2-10 16:37

jivy1212 发表于 2018-2-10 15:40
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2,"    "," "),"   "," ")," ",REPT(" ",50 ...

右拉下拉？请问测试过答案吗，好像不太对唉

jivy1212 · 发表于 2018-2-10 16:52

本帖最后由 jivy1212 于 2018-2-10 16:55 编辑

sayhi95 发表于 2018-2-10 16:37
右拉下拉？请问测试过答案吗，好像不太对唉

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2," "," ")," "," ")," ",REPT(" ",50)),COLUMN(B1)*50-49,50))

粘贴上来有问题，奇怪？再一次就可以了

sayhi95 · 发表于 2018-2-10 16:57

jivy1212 发表于 2018-2-10 16:52
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2," "," ")," "," ")," ",REPT(" ",50)),COLUMN(B1) ...

是我理解错吗？

jivy1212 · 发表于 2018-2-10 17:00

sayhi95 发表于 2018-2-10 16:57
是我理解错吗？

还是上附件吧？？奇怪？？

sayhi95 · 发表于 2018-2-10 17:00

jivy1212 发表于 2018-2-10 16:52
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2," "," ")," "," ")," ",REPT(" ",50)),COLUMN(B1) ...

不是复制粘贴的问题吧

sayhi95 · 发表于 2018-2-10 17:04

jivy1212 发表于 2018-2-10 17:00
还是上附件吧？？奇怪？？

还真是，神奇！好像那个空格变了

jivy1212 · 发表于 2018-2-10 17:08

sayhi95 发表于 2018-2-10 17:04
还真是，神奇！好像那个空格变了

类似RMB的羊符号？？不同字体下会出现一横/二横羊？还是有手有问题……

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

[求助] 不规则空格间数据提取