Excel公式求助！！先谢过了！

加拿大家园论坛

Excel公式求助！！先谢过了！

原文链接：https://forum.iask.ca/threads/687389/

zxcvirgin : 2014-01-03#1

A B
11 1-13 6401279 554012.2 0.2 CLAY - silty, soft, low to medium plastic, grey, moist to wet. 0% 1 CLAY - silty, soft, low to medium plastic, grey, moist to wet.
12 100-3 (100-F) 6493577 587609 0.4 0.4 SAND - silty, some clay, fine grained, compacted, non-low plastic, brown, moist to saturated 6% 3 SAND - silty, some clay, fine grained, compacted, non-low plastic, brown, moist to saturated
13 100-9 6967011 575791 Dry 0.2 SILT - some clay, firm low plastic, light grey, damp to moist 0% 1 SILT - some clay, firm low plastic, light grey, damp to moist
14 Apex 25 -100 6613579 580944 > 4.4 Too deep to be described 100% 8 Too deep to be described
15 Apex 27 +50 6816709 581379 > 4.4 100% 8 N/A

少壮不努力，老大徒伤悲阿。。。- -！

当年没学好Excel，现在被卡住了。。。请教一下各位Excel高手，我现在想要在B column里利用公式从A Column中提取如上内容，在不使用VBA or Macro or Whatever （那东西要学很久。。。让我慢慢来。。。）的前提下，仅利用什么样的公式可以达到这样的效果？最后一个N/A是因为左边的描述栏无内容。

我自己瞎写了一个公式，但是写着写着写不下去了。。。逻辑思维能力有欠缺。。。或者假期休时间长了脑袋木掉了。。。

B11=MID(A11,search(“CLAY”,A11,1)+2,if(iserror(SEARCH("CLAY",A11,1),if(iserror(search("SAND",A11,1),if(iserror(search("SILT",A11,1),"N/A",SEARCH("%",A11,1)-SEARCH("SILT",A11,1))),SEARCH("%",A11,1)-SEARCH("SAND",A11,1))),SEARCH("%",A11,1)-SEARCH("CLAY",A11,1))))

我知道MID(text,start_num,num_chars)，我里面除了第一个CLAY，剩下几个条件都没有Start_num。。。但是我现在就卡在这里了。。。高手帮帮忙。。。万分感谢！！
:wdb5:

zxcvirgin : 2014-01-03#2

回复: Excel公式求助！！先谢过了！

高手们出来支招啊…

Carbsed : 2014-01-03#3

回复: Excel公式求助！！先谢过了！

数字和字母之间的空格分列，应该能实现，你鼓捣一下吧。

lgy : 2014-01-03#4

回复: Excel公式求助！！先谢过了！

Carbsed 说:
数字和字母之间的空格分列，应该能实现，你鼓捣一下吧。

不好实现，因为在column A 中看不出任何规律。

Carbsed : 2014-01-03#5

回复: Excel公式求助！！先谢过了！

lgy 说:
不好实现，因为在column A 中看不出任何规律。

不需要规律，就是去掉前面的数字的部分，留下后面字母的部分。
当然前面也有字母的部分，但是只要能实现分列，那个就好解决了。
字母和数字间的空格分列肯定可以实现，只是我们水平还不够高。

zxcvirgin : 2014-01-03#6

回复: Excel公式求助！！先谢过了！

Carbsed 说:
数字和字母之间的空格分列，应该能实现，你鼓捣一下吧。

lgy 说:
不好实现，因为在column A 中看不出任何规律。

Carbsed 说:
不需要规律，就是去掉前面的数字的部分，留下后面字母的部分。
当然前面也有字母的部分，但是只要能实现分列，那个就好解决了。
字母和数字间的空格分列肯定可以实现，只是我们水平还不够高。

多谢多谢！至少帮我看了。。。

我前面的几列已经基本分出来了，每一个空格前的内容基本的公式如下：

=LEFT(A11,SEARCH(" ",A11,1))

=MID(A11,SEARCH(" ",A11,1)+1,SEARCH(" ",A11,SEARCH(" ",A11,1)+1)-SEARCH(" ",A11,1))

=MID(A11,SEARCH(" ",A11,SEARCH(" ",A11,1)+1)+1,SEARCH(" ",A11,SEARCH(" ",A11,SEARCH(" ",A11,1)+1)+1)-SEARCH(" ",A11,SEARCH(" ",A11,1)+1))

=MID(A11,SEARCH(" ",A11,SEARCH(F11,A11,1))+1,SEARCH(" ",A11,SEARCH(" ",A11,SEARCH(F11,A11,1))+1)-SEARCH(" ",A11,SEARCH(F11,A11,1)))

=MID(A11,SEARCH(" ",A11,SEARCH(G11,A11,1))+1,SEARCH(" ",A11,SEARCH(" ",A11,SEARCH(G11,A11,1))+1)-SEARCH(" ",A11,SEARCH(G11,A11,1)))

但是到了文字描述这列，就是上面单独出来的Column B，因为之前两列很多内容都是一样的，很难区分，所以只好从内容本身着手，好在关键词就几个，就是我上面Search的内容，CLAY SAND SILT，一共2000多行，剩下的手动也不用多久，但是要是全都手动。。。估计我这个周末就折在里面了。。。而且还容易弄错。。。所以还是想靠公式解决。。。

我现在关键的问题在于，我在MID和IFERROR的逻辑关系上有点混乱。。。

大概意思就是，在A column里面，如果搜索出来有CLAY，那就以CLAY作为开始词，一直截取到%之前的所有字节（最好的其实是截取到右边第二个空格之前的内容，我隐约记得Find函数可以实现从右边开始搜索，但是现在印象不深了，所以一会还要去温习一下，但是这个是次要的，主要是我现在逻辑上走不下去了，就是MID函数和IFERROR函数的关系，哪个在外面，哪个在里面。

我现在一共三个条件，就是如果不是CLAY或者如果不是SAND或者如果不是SILT，那么就N/A，但是MID函数里面有三个变量，（Text，Start_Num，Num_Digits），而第二个和第三个都需要用到IFERROR和SEARCH两个函数。。。

头又开始大了。。。高手帮忙。。。 :wdb14:

Henry1976 : 2014-01-03#7

回复: Excel公式求助！！先谢过了！

不是高手，不过看你的描述可以这样做

先以%为分隔符分成A,B列

再用left(A,search(" ",A,len(A)-5)-1)把最后一个空格后的字符扔掉

再用right(A,len(a)-search("clay -",A,1)+1)把关键字前的字符扔掉

如果就几个关键字，那么直接手动刷几次就搞定了。

chris_cc : 2014-01-03#8

回复: Excel公式求助！！先谢过了！

按你的说法，第14列B就应该是N/A啊

zxcvirgin : 2014-01-03#9

回复: Excel公式求助！！先谢过了！

Henry1976 说:
不是高手，不过看你的描述可以这样做

先以%为分隔符分成A,B列

再用left(A,search(" ",A,len(A)-5)-1)把最后一个空格后的字符扔掉

再用right(A,len(a)-search("clay -",A,1)+1)把关键字前的字符扔掉

如果就几个关键字，那么直接手动刷几次就搞定了。

多谢多谢！以%分割不难实现，但是Left那个函数里面从右边搜索第一个空格可以理解，但是Len(A)不一定都是减5的。。。也有6的。。。这个比例还不小。。。Right那个函数手动刷就要用Filter？之前先加一列排个序，然后再过滤关键词？这点倒是可以实现。我试试看去，非常感谢指点！！ :wdb17:

zxcvirgin : 2014-01-03#10

回复: Excel公式求助！！先谢过了！

chris_cc 说:
按你的说法，第14列B就应该是N/A啊

CC看得很细。的确，这也是Column A比较烦人的地方，有三个占大约90%的关键词可以搜索，还有10%的内容中，有的含有描述的，有的没有描述。。。但是有描述的更少一点，这点我打算之后手动了。。。除去三个关键词+N/A，剩下的应该也不麻烦了。关键是这三个关键词及字符串提取我思绪很乱。。。 :wdb14:

lgy : 2014-01-03#11

回复: Excel公式求助！！先谢过了！

个人觉得楼主既然想用公式解决这个问题，肯定不止这几行。一般只有column a的内容有一定规律可循，才达到事半功倍的效果。正是因为用search的时候，想取到中间内容的时候，不管是通过哪种途径都看不到共同的规律可循，才觉得不好写。但我观察这几行内容发现如果能实现search “点数字空格字母"这个功能，就可以写公式了。

jjjvan2 : 2014-01-04#12

回复: Excel公式求助！！先谢过了！

不懂楼主这个表里a列是些什么,是从哪个文本文档里导入过来的数据么?
非得用公式完成吗?
如果导入数据前的格式工整,可以在从文本文档转为excel的时候实现正确分列.

zxcvirgin : 2014-01-04#13

回复: Excel公式求助！！先谢过了！

lgy 说:
个人觉得楼主既然想用公式解决这个问题，肯定不止这几行。一般只有column a的内容有一定规律可循，才达到事半功倍的效果。正是因为用search的时候，想取到中间内容的时候，不管是通过哪种途径都看不到共同的规律可循，才觉得不好写。但我观察这几行内容发现如果能实现search “点数字空格字母"这个功能，就可以写公式了。

多谢多谢！其实现在我打算用公式的原因也在于我觉得其实还是有一点规律可以利用，但年纪大了，逻辑思维混乱，搞不清楚前后的关系。。。

我觉得的规律大概是这样的，

就是Column B肯定是以CLAY或者SAND或者SILT三个词开头的（虽然后面的内容和内容的长度都是不一样的）（其实还有极少数类似上面第四行那种例外，三个词一个都没有，但是我大概看了一下，手动修改的工作量不是很大。。。就决定忽略掉第四行这种可能，先处理1到3行和第5行）；

右边都有一个%可以作为MID函数的被减数（即Search("%",A11,1)，而之前的内容基本都没有%，也就是说右边那个%是唯一的）。我其实想取到右边第二个空格前面一位，但是由于右边字符数的不确定，我打算先以%为界，取出来大部分内容后再处理空格和%之间的内容；

接下来就是在我的MID函数中，我要用到三个不同的Search()内容，而Mid函数本身就有三个参数，其中两个都是不确定的。。。所以就是大概下面这个样子。。

Mid(A1, Search("CLAY",A1,1),Search("%",A1,1)-3-Search("CLAY",A1,1))
Mid(A1, Search("SAND",A1,1),Search("%",A1,1)-3-Search("SAND",A1,1))
Mid(A1, Search("SILT",A1,1),Search("%",A1,1)-3-Search("SILT",A1,1))

这样三种可能的叠加，用ISERROR函数？我现在混乱的就是ISERROR跟MID的函数是什么逻辑关系？哪个在外面哪个在里面？。。。。。。。。。

烦请指点，谢谢！！ :wdb5:

zxcvirgin : 2014-01-04#14

回复: Excel公式求助！！先谢过了！

jjjvan2 说:
不懂楼主这个表里a列是些什么,是从哪个文本文档里导入过来的数据么?
非得用公式完成吗?
如果导入数据前的格式工整,可以在从文本文档转为excel的时候实现正确分列.

。。。。。加密的PDF转的。。。不是照片的PDF我已经觉得万幸了。。。 :wdb14:

Henry1976 : 2014-01-04#15

回复: Excel公式求助！！先谢过了！

zxcvirgin 说:
多谢多谢！其实现在我打算用公式的原因也在于我觉得其实还是有一点规律可以利用，但年纪大了，逻辑思维混乱，搞不清楚前后的关系。。。

我觉得的规律大概是这样的，

就是Column B肯定是以CLAY或者SAND或者SILT三个词开头的（虽然后面的内容和内容的长度都是不一样的）（其实还有极少数类似上面第四行那种例外，三个词一个都没有，但是我大概看了一下，手动修改的工作量不是很大。。。就决定忽略掉第四行这种可能，先处理1到3行和第5行）；

右边都有一个%可以作为MID函数的被减数（即Search("%",A11,1)，而之前的内容基本都没有%，也就是说右边那个%是唯一的）。我其实想取到右边第二个空格前面一位，但是由于右边字符数的不确定，我打算先以%为界，取出来大部分内容后再处理空格和%之间的内容；

接下来就是在我的MID函数中，我要用到三个不同的Search()内容，而Mid函数本身就有三个参数，其中两个都是不确定的。。。所以就是大概下面这个样子。。

Mid(A1, Search("CLAY",A1,1),Search("%",A1,1)-3-Search("CLAY",A1,1))
Mid(A1, Search("SAND",A1,1),Search("%",A1,1)-3-Search("SAND",A1,1))
Mid(A1, Search("SILT",A1,1),Search("%",A1,1)-3-Search("SILT",A1,1))

这样三种可能的叠加，用ISERROR函数？我现在混乱的就是ISERROR跟MID的函数是什么逻辑关系？哪个在外面哪个在里面？。。。。。。。。。

烦请指点，谢谢！！

一定要用mid/iserror的话，

用一个嵌套if搜索3个关键词“CLAY -”等，搜到就返回起始位置，否则返回一个-1（自己可指定）让公式出错。
return=if（iserror(sch"关键词1”),if(iserror(sch"关键词2”),if(iserror(sch"关键词3”), -1,sch"关键词3”),sch"关键词2”）,sch"关键词1”)

sch"a"=search("a",A,1)

然后用mid就行了
mid(A,return,sch("%",A,1)-return+1)

最后去最后一个空格后的字符

chris_cc : 2014-01-04#16

回复: Excel公式求助！！先谢过了！

搞定！喝口水来贴答案。

chris_cc : 2014-01-04#17

回复: Excel公式求助！！先谢过了！

Henry1976 说:
一定要用mid/iserror的话，

用一个嵌套if搜索3个关键词“CLAY -”等，搜到就返回起始位置，否则返回一个-1（自己可指定）让公式出错。
return=if（iserror(sch"关键词1”),if(iserror(sch"关键词2”),if(iserror(sch"关键词3”), -1,sch"关键词3”),sch"关键词2”）,sch"关键词1”)

sch"a"=search("a",A,1)

然后用mid就行了
mid(A,return,sch("%",A,1)-return+1)

最后去最后一个空格后的字符

有关键字的行好办，tricky的是没有关键字的行。

而且如果采用正常的关键字嵌套的话，前提是关键字必须有优先级，这样在同一行中出现多个关键字的时候才能确定以哪个为主。但是你仔细看楼主给出的示例，关键字的优先级是矛盾的。。。。。

chris_cc : 2014-01-04#18

回复: Excel公式求助！！先谢过了！

我的方法比较暴力，既然楼主只关心英文文本，那么选择就简单了：
a）保留英文字符
b) 干掉其它字符

vba好办，循环就行。但是只用excel自带函数的话，就只能硬来了。选a得话，必须hard code 26个英文字母*2，plus 标点符号若干。

选b得话目测只需要过滤掉10个数字以及若干特殊字符。果断选之。。。。

最后一点，根据上面说的，关键字优先级有矛盾之处，好在目测如果关键字大小写敏感的话应该不是问题，果断选择find代替search：前者区分大小写后者不区分。

小雷音 : 2014-01-04#19

回复: Excel公式求助！！先谢过了！

看不懂你说的，太乱，但是你发个表给我，一会就给你弄好

chris_cc : 2014-01-04#20

回复: Excel公式求助！！先谢过了！

解决方案：

第一步，去除噪音数据。
在B列中输入公式：
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"0",""), "1",""), "2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9",""),".",""),">",""),"-",""),"+",""),"%",""),")",""),"(",""),"Apex",""))

第二步，根据关键字位置截取字符
在C列中输入公式：
=MID(B1,IFERROR(IFERROR(IFERROR(FIND("CLAY",B1),FIND("SAND",B1)),FIND("SILT",B1)),1),1000)

搞定！

chris_cc : 2014-01-04#21

回复: Excel公式求助！！先谢过了！

肯定不是完美方案，但限于可以用来归纳规律的数据集有限，只能做到这种程度了。

Henry1976 : 2014-01-04#22

回复: Excel公式求助！！先谢过了！

chris_cc 说:
我的方法比较暴力，既然楼主只关心英文文本，那么选择就简单了：
a）保留英文字符
b) 干掉其它字符

vba好办，循环就行。但是只用excel自带函数的话，就只能硬来了。选a得话，必须hard code 26个英文字母*2，plus 标点符号若干。

选b得话目测只需要过滤掉10个数字以及若干特殊字符。果断选之。。。。

最后一点，根据上面说的，关键字优先级有矛盾之处，好在目测如果关键字大小写敏感的话应该不是问题，果断选择find代替search：前者区分大小写后者不区分。

问题是关键字前也有英文字符，比如dry 咋办？
多个关键字的问题倒是有办法解决：因为“clay -"只有1个。

chris_cc : 2014-01-04#23

回复: Excel公式求助！！先谢过了！

Henry1976 说:
问题是关键字前也有英文字符，比如dry 咋办？
多个关键字的问题倒是有办法解决：因为“clay -"只有1个。

不是这个意思，而是你必须决定 clay sand silt三个关键字的优先级，但示例数据中三个关键字的出现顺序是矛盾的。

Henry1976 : 2014-01-04#24

回复: Excel公式求助！！先谢过了！

chris_cc 说:
不是这个意思，而是你必须决定 clay sand silt三个关键字的优先级，但示例数据中三个关键字的出现顺序是矛盾的。

恩，你直接mid就解决这个问题了，找大写作关键字也是可以。
不过还有个问题，大段英文间的逗号需要保留。所以逗号都得留着。

chris_cc : 2014-01-04#25

回复: Excel公式求助！！先谢过了！

逗号我认为不是噪音，所以保留了。

Henry1976 : 2014-01-04#26

回复: Excel公式求助！！先谢过了！

chris_cc 说:
逗号我认为不是噪音，所以保留了。

chris_cc : 2014-01-04#27

回复: Excel公式求助！！先谢过了！

zxcvirgin 说:
多谢多谢！其实现在我打算用公式的原因也在于我觉得其实还是有一点规律可以利用，但年纪大了，逻辑思维混乱，搞不清楚前后的关系。。。

我觉得的规律大概是这样的，

就是Column B肯定是以CLAY或者SAND或者SILT三个词开头的（虽然后面的内容和内容的长度都是不一样的）（其实还有极少数类似上面第四行那种例外，三个词一个都没有，但是我大概看了一下，手动修改的工作量不是很大。。。就决定忽略掉第四行这种可能，先处理1到3行和第5行）；

右边都有一个%可以作为MID函数的被减数（即Search("%",A11,1)，而之前的内容基本都没有%，也就是说右边那个%是唯一的）。我其实想取到右边第二个空格前面一位，但是由于右边字符数的不确定，我打算先以%为界，取出来大部分内容后再处理空格和%之间的内容；

接下来就是在我的MID函数中，我要用到三个不同的Search()内容，而Mid函数本身就有三个参数，其中两个都是不确定的。。。所以就是大概下面这个样子。。

Mid(A1, Search("CLAY",A1,1),Search("%",A1,1)-3-Search("CLAY",A1,1))
Mid(A1, Search("SAND",A1,1),Search("%",A1,1)-3-Search("SAND",A1,1))
Mid(A1, Search("SILT",A1,1),Search("%",A1,1)-3-Search("SILT",A1,1))

这样三种可能的叠加，用ISERROR函数？我现在混乱的就是ISERROR跟MID的函数是什么逻辑关系？哪个在外面哪个在里面？。。。。。。。。。

烦请指点，谢谢！！

其实你的方法已经很接近了，关键的问题就是我前面提到的，如果不区分大小写的话三个关键字的出现顺序是矛盾的。

比如第一列是clay > silt （silty）
第三列却是 silt > clay

问题来了，究竟是应该先搜索clay还是silt？

如果区分大小写，且假设关键字一定是大写，然后用find代替search就没有这个问题了。

Henry1976 : 2014-01-04#28

回复: Excel公式求助！！先谢过了！

chris_cc 说:
其实你的方法已经很接近了，关键的问题就是我前面提到的，如果不区分大小写的话三个关键字的出现顺序是矛盾的。

比如第一列是clay > silt （silty）
第三列却是 silt > clay

问题来了，究竟是应该先搜索clay还是silt？

如果区分大小写，且假设关键字一定是大写，然后用find代替search就没有这个问题了。

SILT后的“-”也需要保留，不能当噪音抹了。

zhangsifu : 2014-01-04#29

回复: Excel公式求助！！先谢过了！

既然都用到这么复杂的公式了，为什么不用VBA，也不复杂。

估计要用正则表达式才能完成这个功能。
用正则表达式删除掉那几个指定单词之前的所有字符。

或者导出成文本，对文本进行正则表达式替换，然后再导入excel

chris_cc : 2014-01-04#30

回复: Excel公式求助！！先谢过了！

zhangsifu 说:
既然都用到这么复杂的公式了，为什么不用VBA，也不复杂。

估计要用正则表达式才能完成这个功能。
用正则表达式删除掉那几个指定单词之前的所有字符。

或者导出成文本，对文本进行正则表达式替换，然后再导入excel

能用vba或者正则式就好，但楼主的要求就是用excel自带函数。

chris_cc : 2014-01-04#31

回复: Excel公式求助！！先谢过了！

Henry1976 说:
SILT后的“-”也需要保留，不能当噪音抹了。

这个。。。。只好再来一次硬代码了 :wdb14:

把C列的公式改成：

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(B1,IFERROR(IFERROR(IFERROR(FIND("CLAY",B1),FIND("SAND",B1)),FIND("SILT",B1)),1),1000), "CLAY ", "CLAY - "), "SAND ", "SAND - "), "SILT ","SILT - ")

Bugbus : 2014-01-04#32

回复: Excel公式求助！！先谢过了！

try this:

=IF(ISERR( SEARCH("clay - ",A1)), IF(ISERR( SEARCH("sand - ",A1)), IF(ISERR( SEARCH("SILT - ",A1)), "N/A",MID(A1, SEARCH("SILT - ",A1),20)), MID(A1, SEARCH("sand - ",A1),20)),MID(A1, SEARCH("clay - ",A1),20))

20 is the number of characters in MID function, replace it respectively

lgy : 2014-01-04#33

回复: Excel公式求助！！先谢过了！

试下我写的这个，只需要拷贝以下信息就可以了
=IF(ISNUMBER(FIND("CLAY",A1)),"CLAY - silty, soft, low to medium plastic, grey, moist to wet.",IF(ISNUMBER(FIND("SAND",A1)),"SAND - silty, some clay, fine grained, compacted, non-low plastic, brown, moist to saturated ",IF(ISNUMBER(FIND("Dry",A1)),"SILT - some clay, firm low plastic, light grey, damp to moist",IF(ISNUMBER(FIND("Too",A1))," Too deep to be described","N/A")

lgy : 2014-01-04#34

回复: Excel公式求助！！先谢过了！

我自己试了下，应该可以。希望看到更好的办法！但楼主记得要把里面的A1换成A11

zxcvirgin : 2014-01-04#35

回复: Excel公式求助！！先谢过了！

chris_cc 说:
我的方法比较暴力，既然楼主只关心英文文本，那么选择就简单了：
a）保留英文字符
b) 干掉其它字符

vba好办，循环就行。但是只用excel自带函数的话，就只能硬来了。选a得话，必须hard code 26个英文字母*2，plus 标点符号若干。

选b得话目测只需要过滤掉10个数字以及若干特殊字符。果断选之。。。。

最后一点，根据上面说的，关键字优先级有矛盾之处，好在目测如果关键字大小写敏感的话应该不是问题，果断选择find代替search：前者区分大小写后者不区分。

chris_cc 说:
解决方案：

第一步，去除噪音数据。
在B列中输入公式：
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"0",""), "1",""), "2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9",""),".",""),">",""),"-",""),"+",""),"%",""),")",""),"(",""),"Apex",""))

第二步，根据关键字位置截取字符
在C列中输入公式：
=MID(B1,IFERROR(IFERROR(IFERROR(FIND("CLAY",B1),FIND("SAND",B1)),FIND("SILT",B1)),1),1000)

搞定！

哈哈，CC牛！好办法，在另一边也有一个人给我建议这个方法，实验了一下的确可以用，不过需要一点时间做quality check，怕万一有什么特殊字符漏掉了，但是已经省下很多时间了。多谢多谢啊！去操作一下，嘿嘿 :wdb6:

zxcvirgin : 2014-01-04#36

回复: Excel公式求助！！先谢过了！

lgy 说:
试下我写的这个，只需要拷贝以下信息就可以了
=IF(ISNUMBER(FIND("CLAY",A1)),"CLAY - silty, soft, low to medium plastic, grey, moist to wet.",IF(ISNUMBER(FIND("SAND",A1)),"SAND - silty, some clay, fine grained, compacted, non-low plastic, brown, moist to saturated ",IF(ISNUMBER(FIND("Dry",A1)),"SILT - some clay, firm low plastic, light grey, damp to moist",IF(ISNUMBER(FIND("Too",A1))," Too deep to be described","N/A")

lgy 说:
我自己试了下，应该可以。希望看到更好的办法！但楼主记得要把里面的A1换成A11

多谢多谢，但是你还是误会我的意思了，就是我列在主楼B column里面的内容不是仅此三条或者四条，这只是范例，一共2000多行里面基本没有重复的。。。都不一样，只有最开始的关键词，也就是CLAY,SAND,SILT三个词重复，所以后面的内容和长度都是变量。。。所以我才用Search()-Search()来定义Mid函数中的字符串长度，否则就简单了。。。还是感谢你花时间帮我看， :wdb17:

zxcvirgin : 2014-01-04#37

回复: Excel公式求助！！先谢过了！

Bugbus 说:
try this:

=IF(ISERR( SEARCH("clay - ",A1)), IF(ISERR( SEARCH("sand - ",A1)), IF(ISERR( SEARCH("SILT - ",A1)), "N/A",MID(A1, SEARCH("SILT - ",A1),20)), MID(A1, SEARCH("sand - ",A1),20)),MID(A1, SEARCH("clay - ",A1),20))

20 is the number of characters in MID function, replace it respectively

多谢大侠！

但是就是你最后说的这个问题，我觉得很棘手，要提取字符串的长度不是固定的，我现在定义为从%（基本上只有一个，如果有多，我手动修改工作量不会很大）到左侧三个关键词CLAY,SAND,SILT之间字符串的长度，也就是我上面提到的search()-search()，而这里面又存在一个内嵌的iserror关系，也就是三个关键词不知道哪一个会出现，我现在就是被这种嵌套关系给搞晕了。。。 :wdb7:

zxcvirgin : 2014-01-04#38

回复: Excel公式求助！！先谢过了！

Henry1976 说:
问题是关键字前也有英文字符，比如dry 咋办？
多个关键字的问题倒是有办法解决：因为“clay -"只有1个。

多谢！的确，我又去看了一下文件，除了dry，还有一些类似above ground，最开始的序列号带的字母E,X什么的，要抹去有点麻烦，可能要捎带着符号，但是也不能完全排除后面要提取的内容中不含有同样的符号字母组合，就像我上面说的，quality check的量会比较大，但是已经省下不少时间了，呵呵

chris_cc 说:
不是这个意思，而是你必须决定 clay sand silt三个关键字的优先级，但示例数据中三个关键字的出现顺序是矛盾的。

这点我其实有点不太明白，如果是在Mid函数内部的嵌套关系，彼此之间有优先级关系么？我理解的就是一个一个排查，如果第一个没有那就查第二个，第二个没有就第三个，最后都没有就N/A了，我先排查哪个词会影响结果么？要是会的话那就更烦人了。。。 :wdb5:

zxcvirgin : 2014-01-04#39

回复: Excel公式求助！！先谢过了！

chris_cc 说:
其实你的方法已经很接近了，关键的问题就是我前面提到的，如果不区分大小写的话三个关键字的出现顺序是矛盾的。

比如第一列是clay > silt （silty）
第三列却是 silt > clay

问题来了，究竟是应该先搜索clay还是silt？

如果区分大小写，且假设关键字一定是大写，然后用find代替search就没有这个问题了。

嗯，这点提醒的好，我把search 都换成find，应该就能省掉很多麻烦。SILT只出现在关键词部位，Silty会出现在内容中，DRY只会出现在关键词之前，出现在关键词之后的只有dry，所以能区分大小写应该可以排除很多类似的单词。 :wdb11:

zxcvirgin : 2014-01-04#40

回复: Excel公式求助！！先谢过了！

zhangsifu 说:
既然都用到这么复杂的公式了，为什么不用VBA，也不复杂。

估计要用正则表达式才能完成这个功能。
用正则表达式删除掉那几个指定单词之前的所有字符。

或者导出成文本，对文本进行正则表达式替换，然后再导入excel

chris_cc 说:
能用vba或者正则式就好，但楼主的要求就是用excel自带函数。

不是不肯用VBA。。。是不会。。。小时候读书的时候没好好学。。。现在后悔也晚了。。。只能之后有机会慢慢学了。。。但是这个活儿是肯定来不及了。。。 :wdb5:

nfpa70 : 2014-01-04#41

回复: Excel公式求助！！先谢过了！

chris_cc

niuniu200803 : 2014-01-04#42

回复: Excel公式求助！！先谢过了！

Mark

lgy : 2014-01-04#43

回复: Excel公式求助！！先谢过了！

zxcvirgin 说:
多谢多谢！其实现在我打算用公式的原因也在于我觉得其实还是有一点规律可以利用，但年纪大了，逻辑思维混乱，搞不清楚前后的关系。。。

我觉得的规律大概是这样的，

就是Column B肯定是以CLAY或者SAND或者SILT三个词开头的（虽然后面的内容和内容的长度都是不一样的）（其实还有极少数类似上面第四行那种例外，三个词一个都没有，但是我大概看了一下，手动修改的工作量不是很大。。。就决定忽略掉第四行这种可能，先处理1到3行和第5行）；

右边都有一个%可以作为MID函数的被减数（即Search("%",A11,1)，而之前的内容基本都没有%，也就是说右边那个%是唯一的）。我其实想取到右边第二个空格前面一位，但是由于右边字符数的不确定，我打算先以%为界，取出来大部分内容后再处理空格和%之间的内容；

接下来就是在我的MID函数中，我要用到三个不同的Search()内容，而Mid函数本身就有三个参数，其中两个都是不确定的。。。所以就是大概下面这个样子。。

Mid(A1, Search("CLAY",A1,1),Search("%",A1,1)-3-Search("CLAY",A1,1))
Mid(A1, Search("SAND",A1,1),Search("%",A1,1)-3-Search("SAND",A1,1))
Mid(A1, Search("SILT",A1,1),Search("%",A1,1)-3-Search("SILT",A1,1))

这样三种可能的叠加，用ISERROR函数？我现在混乱的就是ISERROR跟MID的函数是什么逻辑关系？哪个在外面哪个在里面？。。。。。。。。。

烦请指点，谢谢！！

按照你的思路和要求，我稍微做了修改，但是如你所说第四行信息如果不符合你自己手动即可。现在贴如下公式：=IF(ISNUMBER(FIND("CLAY",A1)),MID(A1,FIND("CLAY",A1,1),FIND("%",A1,1)-3-FIND("CLAY",A1,1)),IF(ISNUMBER(FIND("SAND",A1)),MID(A1,FIND("SAND",A1,1),FIND("%",A1,1)-3-FIND("SAND",A1,1)),IF(ISNUMBER(FIND("SILT",A1)),MID(A1,FIND("SILT",A1,1),FIND("%",A1,1)-3-FIND("SILT",A1,1)),IF(ISNUMBER(FIND("Too",A1))," Too deep to be described","N/A"))))

Bugbus : 2014-01-04#44

回复: Excel公式求助！！先谢过了！

zxcvirgin 说:
多谢大侠！

但是就是你最后说的这个问题，我觉得很棘手，要提取字符串的长度不是固定的，我现在定义为从%（基本上只有一个，如果有多，我手动修改工作量不会很大）到左侧三个关键词CLAY,SAND,SILT之间字符串的长度，也就是我上面提到的search()-search()，而这里面又存在一个内嵌的iserror关系，也就是三个关键词不知道哪一个会出现，我现在就是被这种嵌套关系给搞晕了。。。

1) 你的提取字符窜的方法是对的，把他用上即可。
2）Search（）-Search（）仅仅出现在MID中。而之前， Search已经被检验过了，有ERROR的话，都不会用到MID了，直接就N/A了。
3）三个关键词都被检查了，没有的话，才用N/A。
4）应该不会出现两个或以上关键词同时出现的情况。 :wdb23:

FBI局长 : 2014-01-06#45

回复: Excel公式求助！！先谢过了！

顶excel

Tom.Yu : 2014-01-06#46

回复: Excel公式求助！！先谢过了！

我也分享一下我的过程（这可以不管关键字，只要规律一致）：
1）第一步我是把后面的数字去掉，得到前半部分字符串，放到B栏
=MID(A2,1,FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1))-1)

2）在前半部分字符串找到最后一个数字，从它后面开始的字符串就是所求结果，放到C栏
=MID(B2,LOOKUP(9,--MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1),ROW(INDIRECT("1:"&LEN(B2))))+1,999)

由于不太懂Excel公式，于是百度了一下：
先搜索“Excel字符串取最后一个空格”，得到http://www.excelpx.com/thread-288080-1-1.html，看到帖子最后一篇是=MID(A2,FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,99)
于是学习了Substitute函数，改造成自己的取倒数第二个空格
再搜索“Excel字符串找最后一个数字”，找到http://club.excelhome.net/thread-606389-1-1.html
这个就可以直接用了。
虽然一知半解，但我总相信靠百度搜索Excel知识，一般还是可以解决问题的。
：）

Tom.Yu : 2014-01-06#47

回复: Excel公式求助！！先谢过了！

当然合并成一个公式也可以，就是嫌长：）

Tom.Yu 说:
我也分享一下我的过程（这可以不管关键字，只要规律一致）：
1）第一步我是把后面的数字去掉，得到前半部分字符串，放到B栏
=MID(A2,1,FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1))-1)

2）在前半部分字符串找到最后一个数字，从它后面开始的字符串就是所求结果，放到C栏
=MID(B2,LOOKUP(9,--MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1),ROW(INDIRECT("1:"&LEN(B2))))+1,999)

由于不太懂Excel公式，于是百度了一下：
先搜索“Excel字符串取最后一个空格”，得到http://www.excelpx.com/thread-288080-1-1.html，看到帖子最后一篇是=MID(A2,FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,99)
于是学习了Substitute函数，改造成自己的取倒数第二个空格
再搜索“Excel字符串找最后一个数字”，找到http://club.excelhome.net/thread-606389-1-1.html
这个就可以直接用了。
虽然一知半解，但我总相信靠百度搜索Excel知识，一般还是可以解决问题的。
：）

zxcvirgin : 2014-01-06#48

回复: Excel公式求助！！先谢过了！

lgy 说:
按照你的思路和要求，我稍微做了修改，但是如你所说第四行信息如果不符合你自己手动即可。现在贴如下公式：=IF(ISNUMBER(FIND("CLAY",A1)),MID(A1,FIND("CLAY",A1,1),FIND("%",A1,1)-3-FIND("CLAY",A1,1)),IF(ISNUMBER(FIND("SAND",A1)),MID(A1,FIND("SAND",A1,1),FIND("%",A1,1)-3-FIND("SAND",A1,1)),IF(ISNUMBER(FIND("SILT",A1)),MID(A1,FIND("SILT",A1,1),FIND("%",A1,1)-3-FIND("SILT",A1,1)),IF(ISNUMBER(FIND("Too",A1))," Too deep to be described","N/A"))))

非常感谢！！按照你的思路，我稍微改了一下，发现基本上差不多了，你里面唯一有一点问题的就是-3，因为%前面的字符数量不均等，我稍微改了一下如下，

=IF(ISNUMBER(FIND("CLAY",A12)),MID(A12,FIND("CLAY",A12,1),LEN(A12)-FIND("CLAY",A12,1)+1),IF(ISNUMBER(FIND("SAND",A12)),MID(A12,FIND("SAND",A12,1),LEN(A12)-FIND("SAND",A12,1)+1),IF(ISNUMBER(FIND("SILT",A12)),MID(A12,FIND("SILT",A12,1),LEN(A12)-FIND("SILT",A12,1)+1),IF(ISNUMBER(FIND("Too",A12)),MID(A12,FIND("Too",A12,1),LEN(A12)-FIND("Too",A12,1)+1),"N/A"))))

这样的话，我就把所有关键词及之后的内容取了出来，然后再加两列，分别取出最后两列的内容，

=RIGHT(A12,LEN(A12)-FIND("|",SUBSTITUTE(A12," ","|",LEN(A12)-LEN(SUBSTITUTE(A12," ",""))-1))+1)

=LEFT(I12,LEN(J12)-LEN(L12))

这样三列的内容就都出来了。但是还是基于你的公式为前提，提示了我很多。非常感谢！！ :wdb6:

zxcvirgin : 2014-01-06#49

回复: Excel公式求助！！先谢过了！

Bugbus 说:
1) 你的提取字符窜的方法是对的，把他用上即可。
2）Search（）-Search（）仅仅出现在MID中。而之前， Search已经被检验过了，有ERROR的话，都不会用到MID了，直接就N/A了。
3）三个关键词都被检查了，没有的话，才用N/A。
4）应该不会出现两个或以上关键词同时出现的情况。

非常感谢！我开始就是在这点上混淆了，关键词搜索和选择字符串长度的逻辑关系，经过这里面朋友的提示，总算搞清楚了，呵呵，谢谢！

zxcvirgin : 2014-01-06#50

回复: Excel公式求助！！先谢过了！

Tom.Yu 说:
我也分享一下我的过程（这可以不管关键字，只要规律一致）：
1）第一步我是把后面的数字去掉，得到前半部分字符串，放到B栏
=MID(A2,1,FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1))-1)

2）在前半部分字符串找到最后一个数字，从它后面开始的字符串就是所求结果，放到C栏
=MID(B2,LOOKUP(9,--MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1),ROW(INDIRECT("1:"&LEN(B2))))+1,999)

由于不太懂Excel公式，于是百度了一下：
先搜索“Excel字符串取最后一个空格”，得到http://www.excelpx.com/thread-288080-1-1.html，看到帖子最后一篇是=MID(A2,FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,99)
于是学习了Substitute函数，改造成自己的取倒数第二个空格
再搜索“Excel字符串找最后一个数字”，找到http://club.excelhome.net/thread-606389-1-1.html
这个就可以直接用了。
虽然一知半解，但我总相信靠百度搜索Excel知识，一般还是可以解决问题的。
：）

Tom.Yu 说:
当然合并成一个公式也可以，就是嫌长：）

非常感谢！！我开始觉得这里面最麻烦的就是各个关键词之间的逻辑关系，因为先用筛选分别求解当然可以，但是就是很麻烦，且容易有遗漏。组成一个公式就要首先搞清楚它们之间的逻辑关系。

我也是google出来的一些思路，加上这边你们的一些good idea，才算基本解决了这个问题。 :wdb6:

不过还是打算有时间学一下VBA。。。发现这个才是治标治本的办法。。。就是不知道这东西有多难学。。。 :wdb5:

Tom.Yu : 2014-01-06#51

回复: Excel公式求助！！先谢过了！

想要学，肯定不难，只需决心：）
学了不用，忘得快。
我之前学了一点函数，长期不用，现在都得靠查百度。
之前也学了一点VBA，也是没怎么用，现在基本忘光。如果工作一直和Excel打交道，学习一下提高效率很有好处的。

zxcvirgin 说:
非常感谢！！我开始觉得这里面最麻烦的就是各个关键词之间的逻辑关系，因为先用筛选分别求解当然可以，但是就是很麻烦，且容易有遗漏。组成一个公式就要首先搞清楚它们之间的逻辑关系。

我也是google出来的一些思路，加上这边你们的一些good idea，才算基本解决了这个问题。

不过还是打算有时间学一下VBA。。。发现这个才是治标治本的办法。。。就是不知道这东西有多难学。。。

macrabbit : 2014-01-06#52

回复: Excel公式求助！！先谢过了！

写个程序，先把excel导出，处理后倒入,java .net都可以
直接套用excel 公式，很容易误点击破坏公司

lgy : 2014-01-06#53

回复: Excel公式求助！！先谢过了！

macrabbit 说:
写个程序，先把excel导出，处理后倒入,java .net都可以
直接套用excel 公式，很容易误点击破坏公司

如果用vba,可以用宏可以解决的。
将这些工作簿放到一个文件夹（只有这些Excel文件，且若打开某一文件，数据就能看见——即不用点其他sheet），建一新Excel，也存到该文件夹。仅打开该新Excel，同时按Alt+F11进入宏界面，点菜单的插入，模块，粘贴代码，然后按F5运行即可。

lgy : 2014-01-06#54

回复: Excel公式求助！！先谢过了！

zxcvirgin 说:
非常感谢！！按照你的思路，我稍微改了一下，发现基本上差不多了，你里面唯一有一点问题的就是-3，因为%前面的字符数量不均等，我稍微改了一下如下，

=IF(ISNUMBER(FIND("CLAY",A12)),MID(A12,FIND("CLAY",A12,1),LEN(A12)-FIND("CLAY",A12,1)+1),IF(ISNUMBER(FIND("SAND",A12)),MID(A12,FIND("SAND",A12,1),LEN(A12)-FIND("SAND",A12,1)+1),IF(ISNUMBER(FIND("SILT",A12)),MID(A12,FIND("SILT",A12,1),LEN(A12)-FIND("SILT",A12,1)+1),IF(ISNUMBER(FIND("Too",A12)),MID(A12,FIND("Too",A12,1),LEN(A12)-FIND("Too",A12,1)+1),"N/A"))))

这样的话，我就把所有关键词及之后的内容取了出来，然后再加两列，分别取出最后两列的内容，

=RIGHT(A12,LEN(A12)-FIND("|",SUBSTITUTE(A12," ","|",LEN(A12)-LEN(SUBSTITUTE(A12," ",""))-1))+1)

=LEFT(I12,LEN(J12)-LEN(L12))

这样三列的内容就都出来了。但是还是基于你的公式为前提，提示了我很多。非常感谢！！

能对楼主有帮助就非常开心了。希望今后多切磋，我也是知道的不多，也是现学现用。前段时间我的manager叫我改一个项目的好多excel里面的title block信息，实在不想一个个打开改，就学习用程序做，很是有意思！

加拿大家园论坛Excel公式求助！！先谢过了！

原文链接：https://forum.iask.ca/threads/687389/

加拿大家园论坛

Excel公式求助！！先谢过了！