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

[chris_cc]

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

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

 

[Henry1976]

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

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

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

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

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

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

 

[chris_cc]

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

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

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

 

[Henry1976]

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

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

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

 

[chris_cc]

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

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

 

[Henry1976]

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

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

 

[chris_cc]

回复: 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的函数是什么逻辑关系？哪个在外面哪个在里面？。。。。。。。。。

烦请指点，谢谢！！

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

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

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

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

 

[Henry1976]

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

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

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

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

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

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

 

[zhangsifu]

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

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

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

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

 

[chris_cc]

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

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

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

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

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

 

[chris_cc]

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

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

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


把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]

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

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

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

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

 

[zxcvirgin]

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

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

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

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

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

解决方案：

第一步，去除噪音数据。
在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，怕万一有什么特殊字符漏掉了，但是已经省下很多时间了。多谢多谢啊！去操作一下，嘿嘿

 

[zxcvirgin]

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

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

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

 

[zxcvirgin]

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

多谢大侠！

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

 

[zxcvirgin]

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

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

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

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

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

 

[zxcvirgin]

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

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

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

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

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

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

 

[zxcvirgin]

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

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

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

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

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

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