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

[zxcvirgin]

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。。。但是我现在就卡在这里了。。。高手帮帮忙。。。万分感谢！！

 

[zxcvirgin]

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

高手们出来支招啊…

 

[Carbsed]

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

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

 

[lgy]

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

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

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

 

[Carbsed]

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

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

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

 

[zxcvirgin]

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

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

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

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

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

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

=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两个函数。。。

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

 

[Henry1976]

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

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

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

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

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

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

 

[chris_cc]

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

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

 

[zxcvirgin]

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

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

先以%为分隔符分成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？之前先加一列排个序，然后再过滤关键词？这点倒是可以实现。我试试看去，非常感谢指点！！

 

[zxcvirgin]

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

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

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

 

[lgy]

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

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

 

[jjjvan2]

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

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

 

[zxcvirgin]

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

个人觉得楼主既然想用公式解决这个问题，肯定不止这几行。一般只有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的函数是什么逻辑关系？哪个在外面哪个在里面？。。。。。。。。。

烦请指点，谢谢！！

 

[zxcvirgin]

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

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

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

 

[Henry1976]

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

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

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

就是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]

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

搞定！喝口水来贴答案。

 

[chris_cc]

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

一定要用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]

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

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

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

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

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

 

[小雷音]

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

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

 

[chris_cc]

回复: 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)


搞定！