Over the last 30 years I have used 16 formula combinations more than others. The below can most likely be done by buttons in Excel but if you rather use formulas then here they are:
- Data Lookup – (compare two data sources to identify same data):
=VLOOKUP(@E:E,A:B,2,FALSE)
- Check for duplicates in a column of data – (Let’s say that you want to be left with all unique values only):
Example 1 (Will leave you with unique data only):
(First arrange data in column order, then apply code, then filter out all ‘XX’ and keep ‘blanks & single X’:
=IF(COUNTIF(A:A,A2)>1,IF(COUNTIF(A$1:A2,A2)=1,”x”,”xx”),””)
Example 2 (Remove all duplicates with Excel button):
- Merge Data In Two Cells – (If you ever need to add prefix or suffix information, or maybe just merge first and last name).
Example 1 (merge with space, hyphen, space between the target data):
=(A2&” – “&B2)
Example 2 (merge without space between the target data):
=A2&B2
- Add fixed value in front of a value in a cell – Let’s say that you would like to add a country-code to an existing phone number:
Example 1: Will add ‘+44, space’ as a suffix to existing cell value:
=”+44 “&A2
- Add fixed value to existing cell and at the same time remove first leading character from existing value:
Example 1: Add country code as prefix and remove the first character from existing cell value:
=”+44″&RIGHT(A2,LEN(A2)-1)
- Remove number of characters BEFORE or at END of data in cell:
Example 1: This formula will remove the 3 first characters from the data in cell:
=RIGHT(A2,LEN(A2)-3)
Example 2: This formula will remove the 3 Last characters from the data in cell:
=LEFT(A2,LEN(A2)-3)
- Lift out first & last name from email – (below will copy the first & last name in to new columns):
Example 1: This will copy First Name in to a new Cell:
=LEFT(LEFT(A2,FIND(“.”,A2)-1),FIND(“@”,A2)-1)
Example 2: This will copy Last Name in to a new Cell:
=LEFT(RIGHT(A2,LEN(A2)-FIND(“.”,A2)),FIND(“@”,RIGHT(A2,LEN(A2)-FIND(“.”,A2)))-1)
- All Characters Upper, Lower Case OR Only first Character Upper Case OR All New words starts with Upper Case – (see variations below):
Example 1: This will add Upper Case to the first Character in data:
=REPLACE(LOWER(A2),1,1,UPPER(LEFT(A2,1)))
Example 2: This will add Upper Case to each new word in data:
=PROPER(A2)
Example 3: This will make ALL characters Upper case in data:
=UPPER(A2)
Example 4: This will make ALL characters Lower case in data:
=LOWER(A2)
- Duplicate Count – (count number of duplicate values in a column):
=COUNTIF(
- Use Formula =COUNTIF( and pick your data range) then add the 4 x ‘$’ as in image):
2. Copy and paste the result as ‘value’ only to get rid of formula then go to Data Tab and pick ‘Remove Duplicates’:
3. You now have your result:
- Comma Separated String – (merge column data into one comma separated string):
=A2&B2 | =CONCAT(C2:C421)
Add ‘,’ to your values with formula: =A2&B2
Merge your values into one cell with: =CONCAT(C2:C421)
Or use formula | =TEXTJOIN(“,”,TRUE, (then add cells)
