CODEWITHSUNDEEP
excel
Justin Kelley
Justin Kelley

Reputation: 107

Check if two cells match, but ignore spaces and periods

I have a formula that is checking if two cells are the same. For example:

 A1: Michael A
 B1: MichaelA
 C1: =IF(A1=B1,"Y","N")

 A2: Justin M.
 B2: Justin M
 C2: =IF(A2=B2,"Y","N")

With my current formulas, both C1 and C2 would return 'N' because of the periods and spaces.

Can I edit this formula to ignore them and return 'Y' on these occasions?

Upvotes: 2

Views: 20399

Answers (1)

Trum
Trum

Reputation: 630

If you only want to ignore those and a formula is what you are after, you can nest substitutes and use an IF exactly as above:

=IF(SUBSTITUTE(SUBSTITUTE(I10," ",""),".","")=SUBSTITUTE(SUBSTITUTE(J10," ",""),".",""),"Y","N")

Upvotes: 6

Related Questions