Identify Duplicates

 

Trick To Identify Duplicates Values In Excel.

The Microsoft Excel COUNTIF Function helps you to identify duplicates in a data range.
To Identify duplicates you can using the following formula : -

=COUNTIF($A$3:$A$10,A3)>1

Also you can identify the duplicates using Conditional formatting in Microsoft Excel, But  Conditional formatting will identify the duplicates all values like If you have 3 duplicates values in a data range that will identify all values. And  The formula will keep 1 value as Non duplicate value  and identify 2 value as duplicates.

Learn More

Extract Name & E-Code from a Text String

How To Extract Name & E-Code from the text string when the text string have like [e.g John(02457)]

Here you can use the below formula first extract the "Name" then extract the "E-Code" from the data table "Name & E-Code"
Formula:-  To Extract Name                                                                                                


=LEFT(A2,FIND("(",A2)-1)

And  the result will display  John
Keep the same above formula & you can change the "(" to "{" , "[", "#" or "@" 


 How This Formula Works ?

( i )  =LEFT(A2,FIND("(",A2)-1)

( ii ) =LEFT("John(02457)",FIND("(",A2)-1)

( iii ) =LEFT("John(02457)",FIND("("John(02457)")-1)

( iv ) =LEFT("John(02457)",5-1)    [Result as:  John(0 ]

( v ) =LEFT("John(02457)",4)        [ Then Left function pull out 4 character from Left ]

( vi ) =John          [ Result ]

Formula:-  To Extract E-Code                                                                                         


=RIGHT(A2,LEN(A2)-LEN(B2))

And  the result will display (02457)

How This Formula Works ?
( i )  =RIGHT(A2,LEN(A2)-LEN(B2))
( ii ) =RIGHT("John(02457)",LEN(A2)-LEN(B2))

( iii ) =RIGHT("John(02457)",LEN("John(02457)")-LEN(B2)) 

( iv ) =RIGHT("John(02457)",11-LEN(B2))

( v )  =RIGHT("John(02457)",11-LEN("John"))

( vi ) =RIGHT("John(02457)",11-4)

( vii ) =RIGHT("John(02457)",7)       [ Then Right function pull out 7 character from Right ]

( viii ) = (02457)     [Result ]

                                                                    ---0---

Create Digital Clock On Excel

=TEXT(HOUR(NOW()),"00")&":"&TEXT(MINUTE(NOW()),"00")&":"&TEXT(SECOND(NOW()),"00")

You can create a clock in Excel 2003,2007,2010 in one or any cells using this formula's for Day, Month & Year you can uses the excel NOW function

=NOW( ) 

The Format the cell according to you need

Formatting Process :-

Ctrl+F1 => Number => Date => Select format.