2

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)))

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

193449542-27309370502-9045796-169794419204

which works perfectly.

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.

Joey Peters
  • 21
  • 2

1 Answers1

4

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))), "")

enter image description here

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))), "")
  • awesome, that works, is there also a way to make sure it only does it when there's 4 sets of numbers (or at least 3 hyphens) Thanks a bunch already!! – Joey Peters Mar 20 '17 at 15:54
  • To check if there are three hyphens, subtract the length of substituting the hyphens with `""` (*subtrahend*) from the length of the original (*minuend*). If the result (*difference*) is 3 then there were three hyphens. –  Mar 20 '17 at 15:57
  • I am sorry but I don't know what subtrahend means and how I would have to make the formula to get the result. – Joey Peters Mar 20 '17 at 16:07
  • A simple google wil quickly define *subtrahend*; I've added the condition with some narrative above. –  Mar 20 '17 at 16:22
  • thanks a million! I did need the cell to include bing or adwords but got rid of "not" and it worked – Joey Peters Mar 20 '17 at 16:27