结合修剪和if公式 [英] Combining trim and if formula

问题描述

我在excel中使用修剪公式:

I am using a trim formula in excel:

TRIM(LEFT(SUBSTITUTE(MID(M2,FIND("|",SUBSTITUTE(M2,"-","|",2))+1,LEN(M2)),"-",REPT(" ",LEN(M2))),LEN(M2)))

这会在4个数字的字符串之间寻找一个数字:

this looks for a number in between a string of 4 numbers:

193449542-27309370502-9045796-169794419204 

效果很好.

当同一行上的另一个单元格包含"bing"或"Adwords"时，我想添加一个if =，并跳过包含它的单元格.

I want to add an if= when a another cell on that same row contains either "bing" or "Adwords" and skip cells that contain it.

推荐答案

在H2中，根据提供的图像，

In H2 per the supplied image,

=IF(NOT(SUM(COUNTIF(J2, "*"&{"bing","Adwords"}&"*"))), TRIM(LEFT(SUBSTITUTE(MID(M2,FIND("|",SUBSTITUTE(M2,"-","|",2))+1,LEN(M2)),"-",REPT(" ",LEN(M2))),LEN(M2))), "")

要添加一个条件，其中M2必须至少包含三个连字符，请使用 AND 并从原始字符的长度(减号)中减去用"(减号)替换连字符的长度.如果结果(差)为3或更大，则至少有三个连字符.

To add a condition where M2 must contain at least three hyphens, use AND and subtract the length of substituting the hyphens with "" (subtrahend) from the length of the original (minuend). If the result (difference) is 3 or more then there are at least three hyphens.

=IF(and((len(m2)-len(substitute(m2, "-", "")))>=3,NOT(SUM(COUNTIF(J2, "*"&{"bing","Adwords"}&"*")))), TRIM(LEFT(SUBSTITUTE(MID(M2,FIND("|",SUBSTITUTE(M2,"-","|",2))+1,LEN(M2)),"-",REPT(" ",LEN(M2))),LEN(M2))), "")

这篇关于结合修剪和if公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！