Excel - Finding duplicate values from different sheets

[BYTEr]

Hi all,

Hoping someone can help.

I have 2 worksheets 'Sheet1' & 'Sheet2' in the same workbook.

In both sheets in column A I have rows of numbers, eg; ' 233572-1'.

What I want to know is if there are any identical values in column A from both sheets and highlight them, or return a value so I know the identical values.

I did initially use the following code:

=COUNTIF(Sheet2!$A:$A, A1)

In Sheet1 which returned a value which worked when I tested it by adding the same random numbers to both sheets but don't seem to be working on any of the real values I have?

I am using Excel 2010.

Thanks in advance.

 

[BYTEr]

Ok, so nothing wrong with the formula but the formatting of the cells, as when I type in the number the formula works, I've tried TRIM but this didn't fix it, I've tried formatting the cells the same etc but unless I manually retype the number it doesn't work, anyone have any ideas?

 

[BYTEr]

So apparently you can use

=TRIM(CLEAN(text))

Which seems to of worked.