CODEWITHSUNDEEP
excelexcel-formula
Judit Casacuberta Bagó
Judit Casacuberta Bagó

Reputation: 21

How to find similar text values within the same column in excel?

Trying to find a way to highlight in the same column values which start with the same 3 characters:

A
Lorem Ipsum
Lorim
Esset
John
Johnathan

On this example it would highlight all but Esset. I've done some research and found a Fuzzy match add-on but it does not work on Mac. https://www.microsoft.com/en-us/download/details.aspx?id=15011

Also tried

=IF(ISNA(MATCH(LEFT(E8,3),LEFT(E:E,3),0)),"No Match",E8)

Thanks!

Upvotes: 0

Views: 11657

Answers (1)

BigBen
BigBen

Reputation: 50008

Use a rule based on COUNTIFS, LEFT, and a wildcard:

=COUNTIFS(A:A,LEFT(A2,3)&"*")>1

enter image description here

Upvotes: 1

Related Questions