IF a1 CONTAINS 2 ".", then A1, otherwise ""

[jeremypyle]

I want a code for if contains that asks for two of the same thing.

For example in b1, I want to say, "IF a1 CONTAINS two references to the character ".", then a1, otherwise ""

For example, if there was

jeremy.com in a1

It doesn't have 2 lots of "." so b1 would be empty

but if www.jeremy.com does have two lots of "."

Can someone please help me with this?

 

[hellebore08]

You will be counting the instance of text in a cell

Assuming G9 contains the text..

enter this formula in any cell..

=IF(SUM(LEN(G9)-LEN(SUBSTITUTE(G9,".","")))/LEN(".")>1,"more than 1", "")

The code will show "more than 1" if it has 2 or more dots ".".. Hope this helps.

cheers!

src:http://support.microsoft.com/kb/187667

-e.rgabrieldoronila

 

[Rick Rothstein]

Give this formula a try...

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,".",""))=2,A1,"")

 

[jeremypyle]

cheers, thanks. That works perfectly!

Your terrific!!

 

[mikerickson]

Perhaps
=REPT(A1, LEN(A1)>LEN(SUBSTITUTE(A1,".","",2)))

 

[jeremypyle]

Thanks for that Mike. I still haven't been able to figure out the problem from yesterday though. If you have a chance, would you mind checking the post I did yesterday?