# Need to see if first 3 values in a cell match any other cells in the same column

Need to see if first 3 values in a cell match any other cells in the column.

for example there are similar emails, and I would like to be able to see which emails are similar.
###### Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Group Finance ManagerCommented:
What do you mean by " values in a cell"
Author Commented:
So like if someone's email starts with abl or a3b -those values or text
Group Finance ManagerCommented:
can ou attach an example file
Author Commented:
I do not believe so due to confidentiality... :/
Commented:
Let's assume your column with data is column A and you're trying to see if the first 3 letters in cell A1 match any other cells.
In a separate cell, let's say C1, type this: =LEFT(A1)
Now highlight column A >
Click Conditional Formatting
> New Rule
> Format Only Cells That Contain
> Change "Specific Value" to "Specific Text"
> Change the next filter to "beginning with"
> In the cell box, type =\$C\$1.
> Click Format and select a color/format to occur if any cell starts with the text value in C1.
> Click OK

Now all the cells with similar first 3 letters (in C1) will be highlighted or formatted according to your needs.
Author Commented:
Column C is only pulling the first text value, how can I expand that to pull the first 3?
Commented:
sorry about that. use this formula

=LEFT(A1, 3)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
Assuming your data is in A Column and it starts from row-1 i.e. A1..

Then you can apply this formula in B1 and drag this to your last cell of the data.

``````=SUMPRODUCT((LEFT(TRIM(\$A\$1:\$A\$40),3)=LEFT(TRIM(A1),3))*1)
``````

Now any values which are >1 are existing in your data more then once i mean the first 3 letters are more then once...

Also presently i'm looking from row-1 to 40 please change the same basis of your requirements...

Saurabh...
Author Commented:
so the first 3 values are not all one type- some are text and some are numbers and text.
Commented:
Sarah K,

This will compare the first three values and give you a answer doesn't matter what combination they are.

Also i understand the data is confidential but if the above formula is not something what you are looking for then can you post some sample file with dummy data with results what you are expecting as it will help to troubleshoot this in a faster manner...

Saurabh...
Author Commented:
It added to the solution.
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.