Excel extarct words before and after a specific word

Excel extarct words before and after a specific word

For example, you have the following text in a cell “A1″ and you’d like to extract the text before the comma (the last name):
“Alam, Afroz”
To extract the last name before the comma “Alam” from A1, use one of the following formula:
=LEFT(A1,(FIND(",",A1,1)-1))
The result: Alam
To extract the first name after the comma from A1, use one of the following formula:
=MID(A1,FIND(",",A1)+2,256)
The result: Afroz

Compare data in Excel in two columns to find duplicates

Compare data in Excel in  two columns to find duplicates

To use a formula to compare the data in two columns:

1. In a new worksheet, enter the following data (leave column B empty):

   X1: 1   Y1:     Z1: 3
   X2: 2   Y2:     Z2: 5
   X3: 3   Y3:     Z3: 8
   X4: 4   Y4:     Z4: 2
   X5: 5   Y5:     Z5: 0
        

2. Type the following formula in cell B1:
   =IF(ISERROR(MATCH(X1,$Z$1:$Z$5,0)),"",X1)
3. Select cells Y1:Y5.
4. The duplicate numbers are displayed in column B, as in the following example:

   X1: 1   Y1:      Z1: 3
   X2: 2   Y2:2     Z2: 5
   X3: 3   Y3:3     Z3: 8
   X4: 4   Y4:      Z4: 2
   X5: 5   Y5:5     Z5: 0